Which rows are returned while using LIMIT with OFFSET in MySQL?

Suppose the LIMIT is 4 and OFFSET is 6 then it will return the rows from 7 to 10 i.e. will end with row 10. The LIMIT 4 and OFFSET 6 returns row 7,8,9,10.

You can understand the above concept by implementing LIMIT and OFFSET. Let us create a table.

mysql> create table LimitOffsettable
   -> (
   -> Id int
   -> );
Query OK, 0 rows affected (0.60 sec)

Let us insert some records in the table. The query is as follows −

Mysql> insert into LimitOffsettable values(1);
Query OK, 1 row affected (0.15 sec)
mysql> insert into LimitOffsettable values(2);
Query OK, 1 row affected (0.21 sec)
mysql> insert into LimitOffsettable values(3);
Query OK, 1 row affected (0.12 sec)
mysql> insert into LimitOffsettable values(4);
Query OK, 1 row affected (0.12 sec)
mysql> insert into LimitOffsettable values(5);
Query OK, 1 row affected (0.12 sec)
mysql> insert into LimitOffsettable values(6);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LimitOffsettable values(7);
Query OK, 1 row affected (0.13 sec)
mysql> insert into LimitOffsettable values(8);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LimitOffsettable values(9);
Query OK, 1 row affected (0.09 sec)
mysql> insert into LimitOffsettable values(10);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LimitOffsettable values(11);
Query OK, 1 row affected (0.12 sec)
mysql> insert into LimitOffsettable values(12);
Query OK, 1 row affected (0.11 sec)
mysql> insert into LimitOffsettable values(13);
Query OK, 1 row affected (0.13 sec)\

You can display all records inserted above with the help of select statement. The query is as follows −

mysql> select *from LimitOffsettable;

Here is the output −

+------+
| Id   |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 6    |
| 7    |
| 8    |
| 9    |
| 10   |
| 11   |
| 12   |
| 13   |
+------+
13 rows in set (0.00 sec)

Implement LIMIT 4 and OFFSET 6 that begins row from 7 to till 10 (i.e. 7,8,9,10).

The query is as follows −

mysql> select *from LimitOffsettable LIMIT 4 OFFSET 6;

Here is the output that returns row −

+------+
| Id   |
+------+
| 7    |
| 8    |
| 9    |
| 10   |
+------+
4 rows in set (0.00 sec)

Ankith Reddy

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• Pagination using MySQL LIMIT, OFFSET?
• Get total number of rows while using LIMIT in MySQL?
• Resolve an error whenever multiple rows are returned in MySQL Benchmark?
• Get a count of total documents with MongoDB while using limit?
• Implement MySQL LIMIT and OFFSET in a single query stating its difference
• MySQL query to return only the rows which are numeric?
• GROUP BY the number of rows returned by GROUP BY in MySQL?
• Multiple WHERE with LIMIT in MySQL?
• What kind of output is returned by MySQL scalar subquery? What are the restrictions on using it with MySQL query?
• How to limit the number of results returned from grep in Linux?
• Which one is preferred in between MySQL EXISTS and IN while using in Subqueries?
• While running MySQL statements in batch mode, how can we print, along with output, which statements are getting executed?
• SELECT last entry without using LIMIT in MySQL?
• Limit the count using GROUP BY in MySQL
• Get another order after limit with MySQL?