How do you select every n-th row from mysql

I have a series of values in a database that I need to pull to create a line chart. Because i dont require high resolution I would like to resample the data by selecting every 5th row from the database.

Solution 1:

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

Solution 2:

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;

Solution 3:

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.

Solution 4:

SET @a = 0;
SELECT * FROM t where (@a := @a + 1) % 2 = 0;