Sorting Data with SQL ORDER BY Clause
In SQL, the ORDER BY clause is used to sort the result set of a query by one or more columns, either in ascending or descending order. Sorting data is crucial when we want to present or analyze information in a specific sequence. Whether you're listing customers by their last name or arranging transactions by date, the ORDER BY clause gives you full control over the ordering of your data.
This chapter will walk you through the fundamentals of the ORDER BY clause, starting with the basics and progressively moving toward more advanced techniques. By the end of this chapter, you will have a comprehensive understanding of how to sort data using SQL.
The Basics of the ORDER BY Clause
The ORDER BY clause is used at the end of a SELECT query to specify how the result set should be sorted. By default, it sorts the data in ascending order.
Syntax:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
column1,column2: Columns used to determine the order of the rows.ASC: Sort in ascending order (default).DESC: Sort in descending order.
Example 1: Basic Ordering
Consider a table employees:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | $6000 |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | HR | $5000 |
| 5 | David Green | Sales | $55001 |
Query:
SELECT name, salary
FROM employees
ORDER BY salary;
Output:
| Name | Salary |
|---|---|
| John Doe | $5000 |
| Mary White | $5000 |
| David Green | $5500 |
| Jane Smith | $6000 |
| Bob Brown | $7000 |
By default, the ORDER BY clause sorts the salary column in ascending order, from lowest to highest.
Sorting in Descending Order
By default, the ORDER BY clause sorts in ascending order, but we can change this by using the DESC keyword to sort in descending order.
Example 2: Sorting in Descending Order
Query:
SELECT name, salary
FROM employees
ORDER BY salary DESC;
Output:
| Name | Salary |
|---|---|
| Bob Brown | $7000 |
| Jane Smith | $6000 |
| David Green | $5500 |
| John Doe | $5000 |
| Mary White | $5000 |
Here, the ORDER BY salary DESC sorts the data in descending order, from highest to lowest salary.
Sorting by Multiple Columns
You can sort by more than one column using the ORDER BY clause. The SQL engine first sorts the rows by the first column and then sorts rows with the same value in the first column by the second column.
Example 3: Sorting by Multiple Columns
Let’s sort the employees by salary and then by name for employees with the same salary.
Query:
SELECT name, department, salary
FROM employees
ORDER BY salary, name;
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Mary White | HR | $5000 |
| David Green | Sales | $5500 |
| Jane Smith | IT | $6000 |
| Bob Brown | IT | $7000 |
The employees are first sorted by salary in ascending order. For employees with the same salary (5000 in this case), they are further sorted by name alphabetically.
Sorting in Mixed Orders (ASC and DESC)
You can specify different sort orders for different columns. For example, you might want to sort by one column in ascending order and another in descending order.
Example 4: Mixed Ordering
Let’s sort by department in ascending order and within each department, by salary in descending order.
Query:
SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;
Output:
| Name | Department | Salary |
|---|---|---|
| John Doe | HR | $5000 |
| Mary White | HR | $5000 |
| Bob Brown | IT | $7000 |
| Jane Smith | IT | $6000 |
| David Green | Sales | $5500 |
The departments are sorted in alphabetical order (HR, IT, Sales), but within each department, the employees are sorted by salary in descending order.
Ordering by Column Position
Instead of specifying the column names, you can use the column’s position in the SELECT statement for sorting. The first column in the SELECT statement is referenced as 1, the second as 2, and so on.
Example 5: Sorting by Column Position
Let’s sort the employees by the second column in the result set, which is the salary column.
Query:
SELECT name, salary
FROM employees
ORDER BY 2 DESC;
Output:
| Name | Salary |
|---|---|
| Bob Brown | $7000 |
| Jane Smith | $6000 |
| David Green | $5500 |
| John Doe | $5000 |
| Mary White | $5000 |
Here, ORDER BY 2 DESC means “sort by the second column in descending order,” which in this case is salary.
NULL Values in Ordering
In SQL, NULL represents missing or unknown values. When you sort a column that contains NULL values, they are handled in a specific way, depending on the database system.
- In ascending order:
NULLvalues usually appear first. - In descending order:
NULLvalues typically appear last.
Example 6: Sorting with NULL Values
Suppose we modify our employees table to include NULL for some salary values:
| ID | Name | Department | Salary |
|---|---|---|---|
| 1 | John Doe | HR | $5000 |
| 2 | Jane Smith | IT | NULL |
| 3 | Bob Brown | IT | $7000 |
| 4 | Mary White | HR | $5000 |
| 5 | David Green | Sales | NULL |
Query:
SELECT name, salary
FROM employees
ORDER BY salary;
Output:
| Name | Salary |
|---|---|
| Jane Smith | NULL |
| David Green | NULL |
| John Doe | $5000 |
| Mary White | $5000 |
| Bob Brown | $7000 |
The NULL values appear first in the result when sorting in ascending order.
Advanced Ordering Techniques
Ordering with Calculated Columns
You can order by a column that is the result of an expression or function.
Example:
Sort employees by the length of their names.
SELECT name, LENGTH(name) AS name_length
FROM employees
ORDER BY name_length;
Output:
| Name | Name Length |
|---|---|
| Bob Brown | 9 |
| Jane Smith | 10 |
| John Doe | 8 |
| Mary White | 10 |
| David Green | 11 |
Here, we use the LENGTH function to calculate the length of each employee’s name and sort the result based on the calculated column name_length.
Ordering with Subqueries
Sometimes, you need to sort data based on a value that is calculated from a subquery.
Example:
Order employees by their salary compared to the department’s average salary.
SELECT name, salary
FROM employees e
ORDER BY salary - (SELECT AVG(salary) FROM employees WHERE department = e.department);
Output:
| Name | Salary |
|---|---|
| John Doe | $5000 |
| Mary White | $5000 |
| David Green | NULL |
| Jane Smith | NULL |
| Bob Brown | $7000 |
This query orders employees by how much their salary deviates from their department’s average salary.
The ORDER BY clause is a powerful tool in SQL that allows you to sort the results of your queries based on one or more columns. From basic ascending and descending ordering to more advanced techniques like sorting by calculated columns or handling NULL values, mastering the ORDER BY clause gives you the ability to control the presentation and analysis of your data.In this chapter, we’ve covered all the important aspects of sorting data using the ORDER BY clause, including handling multiple columns, NULL values, column positions, and advanced use cases. Happy coding !❤️