Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
add comment

1 Answer

up vote 3 down vote accepted

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;
share|improve this answer
1  
Without an ORDER BY? Why not just select half (from all the rows)? –  ypercube Mar 22 '13 at 20:21
    
@ypercube Oh yes, you are right. ORDER BY is of definite necessity. –  RolandoMySQLDBA Mar 22 '13 at 20:58
    
Thank you very much! –  Copy Devil Mar 22 '13 at 20:59
1  
Updated my answer –  RolandoMySQLDBA Mar 22 '13 at 21:01
    
Wow, amazing answer! –  Copy Devil Mar 22 '13 at 22:15
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.