How do you select every n-th row from mysql

Question

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.

Answer 1

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;

Answer 2

The only thing I can think of is this...

SELECT *
FROM Table
LIMIT 1, 5

Which will return the 5th row from the result set. Not sure how to get every fifth row, but this might start you down the path to a solution

Answer 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, * FROM mytable 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.

Answer 4

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

Answer 5

I had been looking for something like this. The answer of Taylor and Bill led me to improve upon their ideas.

table data1 has fields read_date, value we want to select every 2d record from a query limited by a read_date range the name of the derived table is arbitrary and here is called DT

query:

SET @row := 0; SELECT * FROM ( SELECT @row := @row +1 AS rownum, read_date, value FROM data1 WHERE read_date>= 1279771200 AND read_date <= 1281844740 ) as DT WHERE MOD(rownum,2)=0