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.

Limousin

Answer 1

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

Sruti

Can someone provide more information about how this works? For instance the question asked for every 5th row and there is no mention of 5 in the answer. – Victoria

@Victoria replace [n] in the query with 5 to get every 5th row. – Brashy

To expand on this, what if you didn't want to start with the first row but the 2nd for example? – Nervy

@Nervy you would replace @row :=0 with @row :=2 – Octavo

@BinarWeb no, you would change the = 1 to = 2 – Gwendolyn

Answer 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;

Chairman

Also assuming you have no gaps in the sequence, due to delete or rollback. – Teller

This would work for the most part but does not account for deleted rows. – Limousin

Simple and brilliant for some testing :-) – Campanulate

This makes sense if your select retrieves all the data. If you have additional criteria in your select, then it would be difficult to say what data (if any) it would retrieve. – Affectionate

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, 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.

Teller

I was updating my answer to this, and you beat me to it! Good thinking. – Nkvd

unfortunately, this slows down the execution by at least x100 when working with many entries – Resoluble

Answer 4

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

Suitor

This works great for partitioning an arbitrary, read-only table for parallel processing of the rows, and the syntax is super easy to read and understand. You just need to add an ORDER BY on the primary key column to ensure each row is returned just once. – Transubstantiation

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

Retharethink

Thanks, I was looking for this. I needed to somehow check if a certain column in a log table for stored procedures had the same value every second time. Like 'proc starting', 'proc ending'. The sql below will result with 1 if everything is ok. SET @row := 0; SELECT count(distinct Message) FROM ( SELECT @row := @row +1 AS rownum, Message FROM operations.EventLog WHERE LogTime > now() - interval 6 hour and ProcedureName = 'Do_CDR' ) as DT WHERE MOD(rownum,2)=0; – Ambrogino

Answer 6

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.

Superdominant

Answer 7

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.

Kropotkin

Answer 8

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

Bahena

Answer 9

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

Chunk

Thanks, it's close but you're better off doing this: select name from (SELECT @row:=-1) temp, t where (@row:=@row + 1) % 1 = 0; This has two advantages. First, regardless of n, you always get the first row and second, if you make n = 1, you get all values rather than none. (Two changes: the -1 in row: = - 1 and n = 0 instead of n = 1) – Actinouranium