If you have spent some time playing with databases, you will undoubtedly have come across the SQL ORDER BY clause. It’s one of the essential tools in a data engineer’s toolbox, and understanding it is key to being able to manipulate and analyze your data effectively.
What is the SQL ORDER BY Clause?
The SQL ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. If you want to sort the records in descending order, you can use the DESC keyword.
Syntax of ORDER BY
The basic syntax of ORDER BY is as follows:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Here, column1, column2, … are the fields of a table on which we want to sort data.
ASC is used to sort data in ascending order, and DESC is used to sort data in descending order. If none is specified, the default is ASC.
A Practical Example
Let’s consider a simple table named Employees:
| ID | Name | Salary | Department |
|----|------|--------|------------|
| 1 | John | 50000 | Finance |
| 2 | Sara | 60000 | HR |
| 3 | Bob | 70000 | Marketing |
| 4 | Lucy | 55000 | Finance |
| 5 | Mike | 80000 | HR |
1) Sory by Ascending order
Now, let’s say you want to order our Employees by Salary in ascending order. You would do so with the following query
SELECT * FROM Employees ORDER BY Salary ASC;
This would yield the following result
| ID | Name | Salary | Department |
|----|------|--------|------------|
| 1 | John | 50000 | Finance |
| 4 | Lucy | 55000 | Finance |
| 2 | Sara | 60000 | HR |
| 3 | Bob | 70000 | Marketing |
| 5 | Mike | 80000 | HR |
You can see that the employees are now ordered by their salary from lowest to highest.
2) Sory by Descending order
If we want to get the employees sorted by salary, but in descending order (from highest to lowest), we can use the DESC keyword:
SELECT * FROM Employees ORDER BY Salary DESC;
And the result would be
| ID | Name | Salary | Department |
|----|------|--------|------------|
| 5 | Mike | 80000 | HR |
| 3 | Bob | 70000 | Marketing |
| 2 | Sara | 60000 | HR |
| 4 | Lucy | 55000 | Finance |
| 1 | John | 50000 | Finance |
3) Ordering by Multiple Columns
In SQL, we can sort data based on multiple columns. The data will first be sorted by the first column, and then by the next, and so on.
For example, let’s say we want to order our data first by Department, and then by Salary within each department. We would do this with the following SQL statem
SELECT * FROM Employees ORDER BY Department ASC, Salary DESC;
The output will be
| ID | Name | Salary | Department |
|----|------|--------|------------|
| 4 | Lucy | 55000 | Finance |
| 1 | John | 50000 | Finance |
| 5 | Mike | 80000 | HR |
| 2 | Sara | 60000 | HR |
| 3 | Bob | 70000 | Marketing |
The data is sorted alphabetically by Department (Finance, HR, Marketing), and then by Salary within each department, in descending order.
It’s important to note that the ORDER BY clause works on both numeric and text fields. For text fields, it sorts alphabetically, and for numeric fields, it sorts numerically.
SQL ORDER BY with Aggregate Functions
ORDER BY is often used in conjunction with aggregate functions like COUNT(), AVG(), SUM(), MAX(), MIN(), etc.
Let’s say you want to know the total salaries paid to each department and display the departments in the order of their total salaries, in descending order. You can do this with the GROUP BY and ORDER BY clauses combined
SELECT Department, SUM(Salary) as Total_Salary
FROM Employees
GROUP BY Department
ORDER BY Total_Salary DESC;
This would yield
| Department | Total_Salary |
|------------|--------------|
| HR | 140000 |
| Finance | 105000 |
| Marketing | 70000 |
Here, we have used the GROUP BY clause to group the employees by their department, the SUM() function to calculate the total salary for each department, and the ORDER BY clause to sort the departments by the total salary.
ORDER BY with WHERE Clause
The ORDER BY clause can also be used together with the WHERE clause to order a filtered result set. For instance, if we only want to view the ‘HR’ department employees, sorted by salary in descending order
SELECT * FROM Employees
WHERE Department = 'HR'
ORDER BY Salary DESC;
The result
| ID | Name | Salary | Department |
|----|------|--------|------------|
| 5 | Mike | 80000 | HR |
| 2 | Sara | 60000 | HR |
Conclusion
The ORDER BY clause is a powerful tool in SQL that allows you to sort your results in any way you need. Whether you need to sort by one column or many, in ascending or descending order, the ORDER BY clause has you covered.
