CODEWITHSUNDEEP
phpmysqlmysqli
Bastien Bastiens
Bastien Bastiens

Reputation: 419

How can i select every other row?

I would like to know how i can get only every other row from a table.

I tried :

SELECT * FROM table_name ORDER BY id DESC LIMIT 1, 1

but of course i only get the 2nd row and not 2, 4, 6, ...

Is there a solution to do that ?

Thank you in advance for your help.

Upvotes: 2

Views: 111

Answers (2)

david strachan
david strachan

Reputation: 7228

Simpler version

SELECT * FROM table_name WHERE MOD(id,2) = 0 ORDER BY id DESC

SQLFiddle

Change 0 to 1 for odd numbers

Upvotes: 0

Jeremy Smyth
Jeremy Smyth

Reputation: 23493

There's a sneaky trick with user variables you can use:

SELECT * FROM table_name, (SELECT @c := 0) t 
WHERE (@c := @c + 1) % 2 = 0
ORDER BY id DESC;

This works because @c is calculated for every row (in the WHERE clause), and we're only selecting even records (2, 4, 6, ...) through the mod % operator.

If you want odd records (1, 3, 5, ...), change that ...% 2 = 0 to ...% 2 = 1.

Upvotes: 5

Related Questions