This chapter is about how to get data to be displayed in a sorted way and how to restrict the result set to a determined number of records.
Figure 9.1 : Database schema
1. Simple sorting
Let’s assume we’d like to get data about all employees as already done. However we’d like this time to have data to be sorted in alphabetical order by employee’s last name.
SELECT *
FROM Employee AS emp
ORDER BY emp.Last_name ASC
We get the following table.
| {{h}} |
|---|
| {{r}} |
2. Multiple sorting
Let’s assume we’d like to get data about all company’s projects as well as the last name and first name of employees involved in each, as we did before. However we’d like this time to have data to be sorted in alphabetical order first by project name and then by employee’s last name.
SELECT proj.name AS project, emp.Last_name AS lastName, emp.First_name AS firstName
FROM Employee AS emp, Involvement AS inv, Project AS proj
WHERE inv.Project_id = proj.ID AND inv.Employee_id = emp.ID
ORDER BY proj.Name, emp.Last_name ASC
We get the following table.
| {{h}} |
|---|
| {{r}} |
3. Limit
Let’s suppose we’d like to get data about the employees having the second, third and fourth highest salary respectively among all employees.
SELECT emp.Last_name AS lastName, emp.First_name AS firstName, emp.Salary
FROM Employee AS emp
ORDER BY emp.Salary DESC
LIMIT 1,3
We get the following table.
| {{h}} |
|---|
| {{r}} |
Recap
We now are able to sort data in ascending or descending order using ASC and DESC clauses.
We also discussed how to limit the result set of a SQL query using LIMIT. That clause define a range of the records in the result set to be extracted. Its syntax is as follows : LIMIT offset, number of records. In our example, the employee with the highest salary is indexed at position 0, the one with the second highest salary at position 1, the one with the third highest salary at position 2, etc. In our case the offset is 1 and the number of records is 3, thus LIMIT 1,3.
The LIMIT clause above exposed is the way to do in MySQL and MariaDB only. Other RDBMS‘s have a slightly different way to do it.