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.