Here, we are going to see how to retrieve and display nth records from a MySql database table using an SQL query.
MySQL provides OFF SET value allows us to specify which row to start from retrieving data, like if we want to get records starting from 4th row, then the offset will be 3. This is generally used with LIMIT clause.
Syntax
SELECT * FROM table_name LIMIT a OFFSET b;
Here, a is the number of rows that will be returned and b is the row from which data retrieval start.
Below is sample Data
--
-- Table structure for table `employees`
--
CREATE TABLE `employees` (
`employee_id` int(11) NOT NULL,
`employee_name` varchar(100) NOT NULL,
`salary` int(11) NOT NULL,
`department` varchar(50) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--
-- Dumping data for table `employees`
--
INSERT INTO `employees` (`employee_id`, `employee_name`, `salary`, `department`) VALUES
(1, 'Mohan', 10000, 'Sales'),
(2, 'Manoj', 9000, 'Technology'),
(3, 'Manveer', 8000, 'Marketing'),
(4, 'Manpreet', 7000, 'Human Resources'),
(5, 'Manmohan', 6000, 'Administration'),
(6, 'Ravi', 10000, 'Sales'),
(7, 'Rohan', 9000, 'Technology'),
(8, 'Ravindra', 8000, 'Marketing'),
(9, 'Ranveer', 7000, 'Human Resources'),
(10, 'Rahul', 6000, 'Administration'),
(11, 'pappu', 5000, 'Cleaning');
--
-- Indexes for dumped tables
-- Indexes for table `employees`
--
ALTER TABLE `employees`
ADD PRIMARY KEY (`employee_id`);
--
-- AUTO_INCREMENT for dumped tables
-- AUTO_INCREMENT for table `employees`
--
ALTER TABLE `employees`
MODIFY `employee_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=12;
COMMIT;
Examples using MySQL LIMIT OFFSET
1). Retrieve 4th row from the MySQL table
SELECT * FROM `employees` ORDER BY `employee_id` ASC LIMIT 1 OFFSET 3;
Below Show result
2). Retrieve 3 rows start from 4th row MySQL table
SELECT * FROM `employees` ORDER BY `employee_id` ASC LIMIT 3 OFFSET 3 ;
Below Show result
3). Display from the 3rd row from the MySQL table, set limit and order by salary
SELECT * FROM `employees` ORDER BY `salary` ASC LIMIT 1 OFFSET 2;
Below Show result
I hope this article helps you.
Thanks for visiting Inflay.com .