Sometimes you may need to get every nth row in MySQL, or return every nth row in your table. In this article we will look at how to select every nth row in MySQL.
How to Get Every Nth Row in MySQL
We will look at two ways to get every Nth row in a table – when you have an id like column with sequential numbers in it, and when you don’t have one.
Let us say you have the following table sales (id, amount). For our first example, id column contains sequential row ids – 1, 2, 3, …
mysql> create table sales(id int, amount int);
mysql> insert into sales(id,amount)
values(1,100),
(2,300),
(3,250),
(4,150),
(5,200);
mysql> select * from sales;
+----+--------+
| id | amount |
+----+--------+
| 1 | 100 |
| 2 | 300 |
| 3 | 250 |
| 4 | 150 |
| 5 | 200 |
+----+--------+
Bonus Read : How to Add Auto Increment in Existing Table
Here’s the SQL query to select every nth row in MySQL.
mysql> select * from table_name where table_name.id mod n = 0;
In the above query, we basically select every row whose id mod n value evaluates to zero. So we get rows with id n, 2n, 3n, … Replace table_name with your table name, and n with every nth row that you want to return.
Here’s an example to return every 2nd row from sales table.
mysql> select * from sales where sales.id mod 2 = 0; +----+--------+ | id | amount | +----+--------+ | 2 | 300 | | 4 | 150 | +----+--------+
Bonus Read : How to Find nth row in MySQL
If your table does not have an id-like column with sequential numbers stored, then here’s the SQL query to get every nth row from table.
Let’s say you have the following table sales(order_date, amount) which does not have an id-like column.
mysql> create table sales2(order_date date, amount int);
mysql> 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);
mysql> select * from sales2;
+------------+--------+
| order_date | amount |
+------------+--------+
| 2020-10-01 | 100 |
| 2020-10-02 | 300 |
| 2020-10-03 | 250 |
| 2020-10-04 | 150 |
| 2020-10-05 | 200 |
+------------+--------+
Bonus Read : How to Replicate MySQL Database to Another Server
Here’s the SQL query to 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
In the above query, replace [n] with nth row that you want to get, replace [table name] with your table name, and [column name] with your column name.
In the above query, we use nested queries. In the inner query evaluates and assigns row number for each row and stores it in a temporary variable called rownum. The outer query selects rows with rownum mod n value as zero.
Here is the SQL query to select every 2nd row in MySQL.
mysql> SELECT *
FROM (
SELECT
@row := @row +1 AS rownum, amount
FROM (
SELECT @row :=0) r, sales2
) ranked
WHERE rownum % 2 = 0;
+--------+--------+
| rownum | amount |
+--------+--------+
| 2 | 300 |
| 4 | 150 |
+--------+--------+
Hopefully, this article will help you select every nth row in MySQL.
Ubiq makes it easy to visualize data, and monitor them in real-time dashboards. Try Ubiq for free.
