How to Get Every Nth Row in MySQL

In some cases, you may need to get every nth row in MySQL, or return every nth row in your table. This article describes how to select every nth row in MySQL.

Here are two ways to get every Nth row in a table – when there is an id like column with sequential numbers, and when there is not.

Consider the following sales table (id, amount). In our first example, the id column contains sequential row identifiers as follows: 1, 2, 3, etc.

Example:

1.Create Table

create table sales(id int, amount int);

2. Insert data

insert into sales(id,amount)
values(1,100),
(2,300),
(3,250),
(4,150),
(5,200);

3. Select Table

select * from sales;

The following SQL query will select every nth row in MySQL.

select * from table_name where table_name.id mod n = 0;

As you can see from the query above, we are basically selecting all rows whose id mod n value equals zero. This results in rows with ids n, 2n, 3n, … Replace table_name with the name of your table, and n with the number of rows that you wish to retrieve.

Here is an example of how to return every second row from a sales table.

select * from sales where sales.id mod 2 = 0;

Here is the SQL query to retrieve every nth row from a table that does not have an id-like column with sequential numbers stored.

Example:

The following table sales(order_date, amount) does not have a column that is similar to an ID.

1.Create Table

create table sales2(order_date date, amount int);

2. Insert data 

 insert into sales2(order_date,amount)
       values('2020-10-01',100),
       ('2020-10-02',300),
       ('2020-10-03',250),
       ('2020-10-04',150),
       ('2020-10-05',200);

3. Select table

select * from sales2;

The following SQL query will retrieve every nth row in MySQL.

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

As shown above, replace [n] with the nth row to be retrieved, [table name] with the table name, and [column name] with the column name.

We have used nested queries in the above query. During the inner query, the row number is evaluated and assigned for each row and is stored in a temporary variable called rownum. Outer query selects rows with rownum mod n value of zero.

In MySQL, the following SQL query is used to select every second row.

SELECT *
FROM (
SELECT
@row := @row +1 AS rownum, amount
FROM (
SELECT @row :=0) r, sales2
) ranked
WHERE rownum % 2 = 0;