SELECT every second row without auto_increment How can I select every second row in MySQL, without using any data of the table or some auto_increment?
Something like

SELECT EVERY SECOND ROW FROM `table`;


Thank you!
There is no SQL mechanism for select every other row.

You will have to use an user-defined auto increment variable as follows:

ODD

SET @inc = 0;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;


EVEN

SET @inc = 1;
SELECT * FROM
(SELECT *,@inc:=1-@inc autoinc
FROM mytable) A
WHERE autoinc = 1 ORDER BY id;


Give it a Try !!!

MORE INSIGHTS

If the table is very large, try using a LEFT JOIN of id

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id) 
WHERE autoinc = 1;


EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id) A
LEFT JOIN mytable USING (id) 
WHERE autoinc = 1;


PAGINATION

ODD

SET @inc = 0;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id) 
WHERE autoinc = 1;


EVEN

SET @inc = 1;
SELECT * FROM
(SELECT id,@inc:=1-@inc autoinc
FROM mytable ORDER BY id LIMIT 0,20) A
LEFT JOIN mytable USING (id) 
WHERE autoinc = 1;