This section of the MySQL primer 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.
Assignments:
1.List the names of 5 youngest employees in the company.
2.Extract the next 5 entries starting with the 10 row.
3.Display the names and salary of the employee who draws the largest salary.
4.What does the following statement display?
SELECT emp_id, age, perks
from employee_data ORDER BY
perks DESC LIMIT 10;
Possible Answers:
1.mysql> SELECT f_name, l_name, age from
-> employee_data ORDER BY age
-> LIMIT 5;
+———+———-+——+
| f_name | l_name | age |
+———+———-+——+
| Edward | Sakamuro | 25 |
| Mary | Anchor | 26 |
| Anamika | Pandit | 27 |
| Hal | Simlai | 27 |
| Joseph | Irvine | 27 |
+———+———-+——+
5 rows in set (0.00 sec)
2.mysql> SELECT * from employee_data
-> LIMIT 10, 5;
+——–+——–+——–+———————–+——+——+——–+——-+——————-+
| emp_id | f_name | l_name | title | age | yos | salary | perks | email |
+——–+——–+——–+———————–+——+——+——–+——-+——————-+
| 11 | Paul | Simon | Multimedia Programmer | 43 | 2 | 85000 | 12000 | ps@bignet.com |
| 12 | Arthur | Hoopla | Multimedia Programmer | 32 | 1 | 75000 | 15000 | arthur@bignet.com |
| 13 | Kim | Hunter | Senior Web Designer | 32 | 2 | 110000 | 20000 | kim@bignet.com |
| 14 | Roger | Lewis | System Administrator | 35 | 2 | 100000 | 13000 | roger@bignet.com |
| 15 | Danny | Gibson | System Administrator | 34 | 1 | 90000 | 12000 | danny@bignet.com |
+——–+——–+——–+———————–+——+——+——–+——-+——————-+
5 rows in set (0.00 sec)
3.mysql> select f_name, l_name, salary
-> from employee_data
-> ORDER BY salary DESC
-> LIMIT 1;
+——–+——–+——–+
| f_name | l_name | salary |
+——–+——–+——–+
| Manish | Sharma | 200000 |
+——–+——–+——–+
1 row in set (0.01 sec)
4.This lists the id, age and perks of 10 employees who draw the largest perks.
mysql> SELECT emp_id, age, perks
-> from employee_data ORDER BY
-> perks DESC LIMIT 10;
+——–+——+——-+
| emp_id | age | perks |
+——–+——+——-+
| 1 | 28 | 50000 |
| 16 | 36 | 28000 |
| 2 | 32 | 25000 |
| 17 | 30 | 25000 |
| 21 | 36 | 25000 |
| 3 | 32 | 20000 |
| 13 | 32 | 20000 |
| 18 | 27 | 18000 |
| 19 | 27 | 18000 |
| 7 | 34 | 16000 |
+——–+——+——-+
10 rows in set (0.01 sec)