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