# 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.

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
``````

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