Limiting data retrieval

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