Display every Nth row

from the Artful Common Queries page


Display every Nth row in tbl where id is sequential in MySQL before version 4.1:
SELECT id
FROM tbl
GROUP BY id 
HAVING MOD(id, N) = 0;
or
SELECT a.*
FROM tbl a
JOIN tbl b ON a.id = b.id AND MOD(b.id, N ) = 0;
In MySQL 4.1 and later you can also write:
SELECT *
FROM tbl
WHERE ( id, 0 ) IN ( SELECT id, MOD( id, N ) FROM tbl );
or
SELECT *
FROM ( SELECT id FROM tbl ) AS tmp
WHERE MOD( tmp.id, N ) = 0;

Last updated 2 Sep 2015




Return to the Artful Common Queries page