This section of the online MySQL lesson looks at how to limit the number of records displayed by the SELECT statement.
As your tables grow, you’ll find a need to display only a subset of data. This can be achieved with the LIMIT clause.
For example, to list only the names of first 5 employees in our table, we use LIMIT with 5 as argument.
SELECT f_name, l_name from employee_data LIMIT 5; +---------+--------+ | f_name | l_name | +---------+--------+ | Manish | Sharma | | John | Hagan | | Ganesh | Pillai | | Anamika | Pandit | | Mary | Anchor | +---------+--------+ 5 rows in set (0.01 sec)
These are the first five entries in our table.
You can couple LIMIT with ORDER BY. Thus, the following displays the 4 senior most employees.
SELECT f_name, l_name, age from employee_data ORDER BY age DESC LIMIT 4; +--------+----------+------+ | f_name | l_name | age | +--------+----------+------+ | Paul | Simon | 43 | | Mike | Harper | 36 | | Peter | Champion | 36 | | Roger | Lewis | 35 | +--------+----------+------+ 4 rows in set (0.00 sec)
Cool, yeh?
Similarly, we can list the two youngest employees.
SELECT f_name, l_name, age from employee_data ORDER BY age LIMIT 2; +--------+----------+------+ | f_name | l_name | age | +--------+----------+------+ | Edward | Sakamuro | 25 | | Mary | Anchor | 26 | +--------+----------+------+ 2 rows in set (0.01 sec)
Extracting Subsets
Limit can also be used to extract a subset of data by providing an additional argument.
The general form of this LIMIT is:
SELECT (whatever) from table LIMIT starting row, Number to extract;
SELECT f_name, l_name from employee_data LIMIT 6,3; +--------+------------+ | f_name | l_name | +--------+------------+ | John | MacFarland | | Edward | Sakamuro | | Alok | Nanda | +--------+------------+ 3 rows in set (0.00 sec)
This extracts 3 rows starting from the sixth row.
