MySQL Select Nth Row

“When working with MySQL databases, you may encounter an instance where you must fetch an nth row from a specific table. Instead of selecting all columns or manually searching for ways to perform filters, fetching a row at a particular position reduces the database overhead and manual operation.”

This post will show you how to fetch a record at the nth position using MySQL built-in features.

Let’s dive in.

Create Sample Data

Before learning how to fetch the nth row from a given table, let us set up some sample database and table.

Feel free to skip this section if you have an existing table and records. Otherwise, you can use the code snippet shown below:

create database shop;

use shop;

drop table if exists products;

create table products(
id int auto_increment primary key,
product_name varchar(100) not null,
provider varchar(50),
price float not null,
quantity int not null,
available bool
);

insert
into
shop.products (product_name,
provider,
price,
quantity,
available)
values ('Apple MacBook Air',
'Apple Inc',
1198.99,
100,
true),
('Google Pixel 6a',
'Google Inc',
449.00,
500,
true),
('DJI Mini 3 Drone',
'DJI',
759.90,
500,
true),
('Sony WH-1000XM5',
'Sony Inc',
399.90,
300,
true),
('Samsung Galaxy Z Fold4',
'Samsung Inc',
1799.99,
900,
false),
('Valve Steam Deck',
'Valve Inc',
579.99,
100,
false);

We can then check the records in the table as follows:

SELECT * FROM shop.products;

The resulting table is as shown:

MySQL Return Nth Row Using Limit Clause

The LIMIT clause in MySQL allows us to specify a start and stop row enabling us to fetch an nth row using the N-1, 1 syntax.

The query syntax is as shown:

SELECT * FROM tblTesting LIMIT N-1, 1

For example, suppose you wish to retrieve the 5^th column from the products table we created earlier. We can run the query as shown:

select
*
from
shop.products
limit 4,
1;

The above statement should return:

+----+----------------+-------------+---------+----------+-----------+
| id | product_name | provider | price | quantity | available |
+----+----------------+-------------+---------+----------+-----------+
| 5 | Samsung Galaxy Z Fold4 | Samsung Inc | 1799.99 |900 | 0 |
+----+----------------+-------------+---------+----------+-----------+
1 row in set (0.00 sec)

In this case, the query above returns the 5^th row.

Closing

In this article, you learned how to fetch the nth row in a given table using the MySQL LIMIT clause.

Thanks for reading!!

MySQL Select Nth Row

Create Sample Data

MySQL Return Nth Row Using Limit Clause

Closing

About the author

John Otieno