Display the nth row from MySQL table

In some case, we need to display records of database table from the specified row. MySQL provides OFFSET to return records starting from the specified row, like if we want to get records starting from 3rd row, then the offset will be 2. This is generally used with LIMIT clause.

When we use OFFSET with LIMIT in the select statement, it first skips the rows specified in the OFFSET and then LIMIT clause limits the number of rows return.

Syntax Of OFFSET

SELECT * FROM tbl_name 
LIMIT m OFFSET n

Here, m is the number of rows that will be returned and n is the row from which data retrieval start.

Example of OFFSET

Suppose, we have the following records -

CREATE TABLE IF NOT EXISTS `empdata` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(25) NOT NULL,
  `address` varchar(100) NOT NULL,
  `phone` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=latin1;

INSERT INTO `empdata` (`id`, `name`, `address`, `phone`) VALUES
(6, 'Lussi', 'K-91, Roy Apartment', 909030309),
(4, 'Joya', '15, CP Colony', 342345329),
(5, 'Ammy', '25, JP Colony', 239848342),
(2, 'Priska', '122, JP Colony', 890040908),
(3, 'Abhi', '5, Bank Street', 675748389),
(1, 'Anjali', '121, Vakundh Dham', 840932345);

These are the examples using MySQL OFFSET

Display the 3rd row from the MySQL 'empdata' table

SELECT * FROM `empdata` ORDER BY ID LIMIT 1 OFFSET 2;

This returns the following output -

+----+------+----------------+-----------+
| id | name | address        | phone     |
+----+------+----------------+-----------+
|  3 | Abhi | 5, Bank Street | 675748389 |
+----+------+----------------+-----------+

Display from the 4rd row from the MySQL 'empdata' table, set limit 3

SELECT * FROM `empdata` ORDER BY ID LIMIT 3 OFFSET 3;

This returns the following output -

+----+-------+---------------------+-----------+
| id | name  | address             | phone     |
+----+-------+---------------------+-----------+
|  4 | Joya  | 15, CP Colony       | 342345329 |
|  5 | Ammy  | 25, JP Colony       | 239848342 |
|  6 | Lussi | K-91, Roy Apartment | 909030309 |
+----+-------+---------------------+-----------+

Display from the 5th row from the MySQL 'empdata' table, set limit 2 and order by name

SELECT * FROM `empdata` ORDER BY name LIMIT 2 OFFSET 4;

This returns the following output -

+----+--------+---------------------+-----------+
| id | name   | address             | phone     |
+----+--------+---------------------+-----------+
|  6 | Lussi  | K-91, Roy Apartment | 909030309 |
|  2 | Priska | 122, JP Colony      | 890040908 |
+----+--------+---------------------+-----------+