Syntax error How to return the nth record from MySQL query?

How to return the nth record from MySQL query?

To get the nth record from MySQL query, you can use LIMIT. The syntax is as follows −

select *from yourTableName order by yourColumnName limit n,1;

To understand the above syntax, let us create a table. The following is the query to create a table −

mysql> create table NthRecordDemo
   −> (
   −> Id int,
   −> Name varchar(200)
   −> );
Query OK, 0 rows affected (0.92 sec)

Insert some records in the table using the following query −

mysql> insert into NthRecordDemo values(100,'John');
Query OK, 1 row affected (0.09 sec)

mysql> insert into NthRecordDemo values(101,'Bob');
Query OK, 1 row affected (0.14 sec)

mysql> insert into NthRecordDemo values(102,'Carol');
Query OK, 1 row affected (0.22 sec)

mysql> insert into NthRecordDemo values(103,'Smith');
Query OK, 1 row affected (0.18 sec)

mysql> insert into NthRecordDemo values(104,'Johnson');
Query OK, 1 row affected (0.16 sec)

mysql> insert into NthRecordDemo values(105,'Sam');
Query OK, 1 row affected (0.16 sec)

mysql> insert into NthRecordDemo values(106,'David');
Query OK, 1 row affected (0.13 sec)

Display all records from the table with the help of select statement. The query is as follows −

mysql> select *from NthRecordDemo;

The following is the output −

+------+---------+
| Id   | Name    |
+------+---------+
| 100  | John    |
| 101  | Bob     |
| 102  | Carol   |
| 103  | Smith   |
| 104  | Johnson |
| 105  | Sam     |
| 106  | David   |
+------+---------+
7 rows in set (0.00 sec)

Use the following query to get nth record from the table −

mysql> select *from NthRecordDemo order by Id limit 6,1;

The following is the output −

+------+-------+
| Id   | Name  |
+------+-------+
| 106  | David |
+------+-------+
1 row in set (0.00 sec)
Updated on: 2019-07-30T22:30:24+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements