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.

58105 次浏览
小开

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
小开

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
小开 
SET @a = 0;
SELECT * FROM t where (@a := @a + 1) % 2 = 0;
小开 
SELECT *
FROM (
SELECT @row := @row +1 AS rownum, posts.*
FROM (
SELECT @row :=0) r, posts
) ranked
WHERE rownum %3 = 1

where posts is my table.

小开

You can use this query,

set @n=2; <!-- nth row -->
select * from (SELECT t.*,
@rowid := @rowid + 1 AS ID
FROM TABLE t,
(SELECT @rowid := 0) dummy) A where A.ID mod @n = 0;

or you can replace n with your nth value

小开

If you don't require the row number in the result set you can simplify the query.

SELECT
[column name]
FROM
(SELECT @row:=0) temp,
[table name]
WHERE (@row:=@row + 1) % [n] = 1

Replace the following placeholders:

  1. Replace [column name] with a list of columns you need to fetch.
  2. Replace [table name] with the name of your table.
  3. Replace [n] with a number. e.g. if you need every 5th row, replace it with 5
小开

If you're using MariaDB 10.2, MySQL 8 or later, you can do this more efficiency, and I think more clearly, using common table expressions and window functions.

WITH ordering AS (
SELECT ROW_NUMBER() OVER (ORDER BY name) AS n, example.*
FROM example ORDER BY name
)
SELECT * FROM ordering WHERE MOD(n, 5) = 0;

Conceptually, this creates a temporary table with the contents of the example table ordered by the name field, adds an additional field called n which is the row number, and then fetches only those rows with numbers which are exactly divisible by 5, i.e. every 5th row. In practice, the database engine is often able to optimise this better than that. But even if it doesn't optimise it any further, I think it's clearer than using user variables iteratively as you had to in earlier versions of MySQL.


提交答案