MySQL – Limit Clause


MySQL Limit Clause is used to limit the responses generated by the select command. When a condition is applied on a table, It return all the rows following the rules. Using Limit clause results can be restricted.

The General Syntax of Select statement with Limit clause

SELECT field1, field2,...fieldN 
FROM table_name1, table_name2...
[WHERE Clause]
[ORDER BY field1, field2 <ASC/DESC>]
[OFFSET M ][LIMIT N]

Sample Database Table

mysql> select * from student;
+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 108   | pranav  | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |
| 113   | anoop   | human  |  NULL | NULL  |    NULL | NULL   |   NULL |
+-------+---------+--------+-------+-------+---------+--------+--------+

The command to Limit output

mysql> select * from student
    -> limit 4;

MySQL command fetches all the records but display only the first four results.

+-------+--------+--------+-------+-------+---------+--------+--------+
| admno | name   | stream | marks | grade | fees    | gender | gameID |
+-------+--------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit   | sci    | 56.50 | B     | 1850.35 | M      |      1 |
| 103   | mohit  | bio    | 46.50 | C     | 1980.90 | M      |      3 |
| 104   | mannat | human  | 67.50 | A     | 1420.15 | F      |      4 |
+-------+--------+--------+-------+-------+---------+--------+--------+
4 rows in set (0.00 sec)

using offset clause with the Limit clause in select statement

offset clause can skip the number of records from the top before displaying them on the screen. But offset clause can not be used standalone.

mysql> select * from student LIMIT 5 OFFSET 4;
+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 108   | pranav  | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |
+-------+---------+--------+-------+-------+---------+--------+--------+
5 rows in set (0.00 sec)

Limit clause with Order By clause

mysql> select * from student
    -> order by name
    -> LIMIT 5 OFFSET 5;
+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 108   | pranav  | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |
+-------+---------+--------+-------+-------+---------+--------+--------+
5 rows in set (0.00 sec)

Suggested reading along with Limit Clause is – MySQL Where clause and MySQL Order By Clause.

Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.