How do you select every n-th row from mysql

You could try mod 5 to get rows where the ID is multiple of 5. (Assuming you have some sort of ID column that's sequential.)

select * from table where table.id mod 5 = 0;

Since you said you're using MySQL, you can use user variables to create a continuous row numbering. You do have to put that in a derived table (subquery) though.

SET @x := 0;
SELECT *
FROM (SELECT (@x:=@x+1) AS x, mt.* FROM mytable mt ORDER BY RAND()) t
WHERE x MOD 5 = 0;

I added ORDER BY RAND() to get a pseudorandom sampling, instead of allowing every fifth row of the unordered table to be in the sample every time.


An anonymous user tried to edit this to change x MOD 5 = 0 to x MOD 5 = 1. I have changed it back to my original.

For the record, one can use any value between 0 and 4 in that condition, and there's no reason to prefer one value over another.


SELECT * 
FROM ( 
    SELECT 
        @row := @row +1 AS rownum, [column name] 
    FROM ( 
        SELECT @row :=0) r, [table name] 
    ) ranked 
WHERE rownum % [n] = 1 

Tags:

Mysql