select every other row in MySQL without depending on any ID?

Considering following table that doesn’t have any primary key, can I select every other row?

col1      col2
 2         a
 1         b
 3         c
 12        g

first select must find: 2, 3

second select must find: 1, 12

is that possible?

Here is Solutions:

We have many solutions to this problem, But we recommend you to use the first solution because it is tested & true solution that will 100% work for you.

Solution 1

In unique MySQL fashion:

select  *
from    (
        select  *
        ,       @rn := @rn + 1 as rn
        from    Table1
        join    (select @rn := 0) i
        ) s
where   rn mod 2 = 0 -- Use = 1 for the other set

Example at SQL Fiddle.

Solution 2

Try this. I’ve adapted it from the answer linked below.
I tested it on SQLFiddle and it appears to work.

http://sqlfiddle.com/#!2/0bccf/28
http://sqlfiddle.com/#!2/0bccf/29

Odd Rows:

SELECT x.*
FROM (
     SELECT @rownum:[email protected]+1 rownum, t.*
     FROM (SELECT @rownum:=0) r, table t
) x
WHERE MOD(x.rownum, 2) = 1

Even Rows:

SELECT x.*
FROM (
     SELECT @rownum:[email protected]+1 rownum, t.*
     FROM (SELECT @rownum:=0) r, table t
) x
WHERE MOD(x.rownum, 2) = 0

Adapted from:
MySQL row number

Solution 3

yes possible using temp variable

Example :

set @a := 0;
select * from car_m_city  WHERE mod((@a:[email protected]+1), 2) = 1

Explanation :

here in sql we declare @a( set @a := 0;) temp variable.(@a:[email protected]+1) now @a increment by 1.jsut like simple way to check odd or even

mod((@a:[email protected]+1), 2) = 1 for odd data

mod((@a:[email protected]+1), 2) = 0 for even data

Solution 4

This works for me.

    SET @row_number = 0;

    select* from (
        SELECT 
        (@row_number:[email protected]_number + 1) AS num, col1,col2
    FROM
        TABLE1
        ) as t WHERE num%2=0

You can use mod 1 for odd or mod 0 for even rows

Solution 5

This should work for MySQL:

SELECT col1, col2
FROM (
   SELECT col1, col2, @rowNumber:[email protected]+ 1 rn
   FROM YourTable
      JOIN (SELECT @rowNumber:= 0) r
) t 
WHERE rn % 2 = 1

This uses % which is the MOD operator.

And here is the sample fiddle: http://sqlfiddle.com/#!2/cd31b/2

Note: Use and implement solution 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply