.jpeg)
The ORDER BY clause is used to sort the result of a query. It can arrange rows in ascending or descending order based on one or more columns. The ORDER BY clause sorts values in ascending order by default. When we want the results in descending order, we can use the DESC keyword.
For example, if a table contains employee salaries, you can use order by to display the employees from the lowest salary to the highest salary or from the highest salary to the lowest.
Syntax
It has the following syntax.
SELECT column1, column2, ... FROM table_name ORDER BY column_name [ASC | DESC];
Here:
- SELECT specifies the columns to show in the result.
- FROM specifies the table from which the data is taken.
- ORDER BY tells SQL which column to use for sorting the results.
- ASC arranges the values from smallest to largest or A to Z.
- DESC arranges the values from largest to smallest or Z to A.
Example Table
We will use the following employees table to explain the examples in this article.
| id | name | department | salary | city |
|---|---|---|---|---|
| 1 | Rahul | IT | 50000 | Delhi |
| 2 | Priya | HR | 45000 | Noida |
| 3 | Amit | IT | 65000 | Delhi |
| 4 | Neha | Sales | 40000 | Mumbai |
| 5 | Rohan | HR | 55000 | Noida |
ORDER BY in Ascending Order
To sort the employee records by salary from the lowest to the highest, use ORDER BY with ASC.
Example
SELECT name, salary FROM employees ORDER BY salary ASC;
Output
| name | salary |
|---|---|
| Neha | 40000 |
| Priya | 45000 |
| Rahul | 50000 |
| Rohan | 55000 |
| Amit | 65000 |
Explanation
In this example, we use the ORDER BY clause with salary to arrange the employees based on their salaries. The ASC keyword sorts the salaries from the lowest to the highest value. Therefore, Neha appears first with a salary of 40000, while Amit appears last with a salary of 65000.
ORDER BY in Descending Order
We use the DESC keyword with the ORDER BY clause when we want to display the highest values first.
Example
SELECT name, salary FROM employees ORDER BY salary DESC;
Output
| name | salary |
|---|---|
| Amit | 65000 |
| Rohan | 55000 |
| Rahul | 50000 |
| Priya | 45000 |
| Neha | 40000 |
Explanation
Here, the salaries are arranged from highest to lowest because we use the DESC keyword.
ORDER BY with Text Values
We can also use the ORDER BY clause to sort text values alphabetically.
Example
SELECT name, city FROM employees ORDER BY name ASC;
Output
| name | city |
|---|---|
| Amit | Delhi |
| Neha | Mumbai |
| Priya | Noida |
| Rahul | Delhi |
| Rohan | Noida |
Explanation
In this example, we use the ORDER BY clause with the name column to sort the employee names alphabetically. The ASC keyword arranges the names from A to Z, so Amit appears first, and Rohan appears last.
ORDER BY with Multiple Columns
We can sort the result using more than one column. This is useful when we want to apply a second sorting rule when two or more rows have the same value in the first column.
Example
Suppose we want to sort employees by department first and then by salary.
SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;
Output
| name | department | salary |
|---|---|---|
| Rohan | HR | 55000 |
| Priya | HR | 45000 |
| Amit | IT | 65000 |
| Rahul | IT | 50000 |
| Neha | Sales | 40000 |
Explanation
In this example, we use the ORDER BY clause with two columns. First, the department column is sorted alphabetically using ASC. Then, employees within each department are sorted by salary from highest to lowest using DESC.
ORDER BY with Different Sort Directions
We can use different sorting directions for each column in the ORDER BY clause. This allows one column to be sorted in ascending order and another in descending order.
Example
SELECT name, department, salary FROM employees ORDER BY department ASC, salary DESC;
ORDER BY with WHERE Clause
You can use ORDER BY with the WHERE clause. The WHERE clause first filters the rows, and ORDER BY then sorts the filtered result.
Example
SELECT name, salary FROM employees WHERE department = 'IT' ORDER BY salary DESC;
Output
| name | salary |
|---|---|
| Amit | 65000 |
| Rahul | 50000 |
Explanation
The WHERE clause selects only employees from the IT department. The ORDER BY clause then sorts those employees by salary from highest to lowest.
ORDER BY with SELECT DISTINCT
We can use ORDER BY with the DISTINCT keyword when we want to display only unique values in a sorted order.
Example
SELECT DISTINCT city FROM employees ORDER BY city ASC;
Output
| city |
|---|
| Delhi |
| Mumbai |
| Noida |
Explanation
In this example, we use DISTINCT to remove duplicate city names from the result. Then, we use the ORDER BY clause with ASC to arrange the unique city names alphabetically from A to Z.
ORDER BY with Aggregate Functions
We can also use ORDER BY with aggregate functions such as COUNT(), SUM(), and AVG(). This is useful when we want to sort grouped results based on a calculated value. For example, we can count the employees in each department and then sort the departments by the number of employees.
Example
SELECT department, COUNT(*) AS total_employees FROM employees GROUP BY department ORDER BY total_employees DESC;
Output
| department | total_employees |
|---|---|
| HR | 2 |
| IT | 2 |
| Sales | 1 |
Explanation
In this example, we use GROUP BY to create a separate group for each department. The COUNT() function counts the employees in each department. Then, the ORDER BY clause sorts the departments based on total_employees from highest to lowest.
ORDER BY with Column Alias
We can use a column alias in the ORDER BY clause. This is useful when the column contains a calculated value.
Example
SELECT name, salary * 12 AS annual_salary FROM employees ORDER BY annual_salary DESC;
Output
| name | annual_salary |
|---|---|
| Amit | 780000 |
| Rohan | 660000 |
| Rahul | 600000 |
| Priya | 540000 |
| Neha | 480000 |
Explanation
In this example, we calculate the yearly salary by multiplying salary by 12. We give this calculated column the alias annual_salary. Then, we use this alias in the ORDER BY clause to sort the employees from the highest annual salary to the lowest.
ORDER BY Using Column Position
We can use the position of a column in the SELECT statement with ORDER BY to sort the result. Some SQL database systems support this way of sorting.
Example
SELECT name, salary FROM employees ORDER BY 2 DESC;
Output
| name | salary |
|---|---|
| Amit | 65000 |
| Rohan | 55000 |
| Rahul | 50000 |
| Priya | 45000 |
| Neha | 40000 |
Explanation
In this example, name is the first column and salary is the second column in the SELECT statement. So, ORDER BY 2 DESC means we are sorting the result using the second column, which is salary. The DESC keyword arranges the salaries from highest to lowest.
ORDER BY with NULL Values
NULL is used when a value is missing or not known in SQL. When we use ORDER BY on a column that contains NULL values, the NULL values may appear at the beginning or at the end of the result. Their position depends on the database system.
For example, if some employees do not have a salary value, their salary will be NULL. When we sort the employees by salary, the database decides where these NULL values should appear.
Example
SELECT name, salary FROM employees ORDER BY salary ASC NULLS LAST; Explanation:
In this example, we use ORDER BY to arrange the salaries from lowest to highest. The NULLS LAST option keeps the employees with no salary value at the end of the result.
ORDER BY with LIMIT
We can use ORDER BY with LIMIT when we want to return only a specific number of rows from a sorted result.
Example
Let us take an example to find the three employee with the highest salaries.
SELECT name, salary FROM employees ORDER BY salary DESC LIMIT 3;
Output
| name | salary |
|---|---|
| Amit | 65000 |
| Rohan | 55000 |
| Rahul | 50000 |
Explanation
In this example, we first use ORDER BY to arrange the employees by salary from highest to lowest. Then, LIMIT 3 returns only the first three rows from the sorted result.
ORDER BY with JOIN
The JOIN clause is used to combine data from two or more tables. We can use ORDER BY with JOIN to sort the combined result.
Example
SELECT employees.name, departments.department_name FROM employees JOIN departments ON employees.department_id = departments.id ORDER BY employees.name ASC;
Explanation
In this example, we use JOIN to combine data from the employees and departments tables. The ORDER BY clause then sorts the joined result alphabetically by the employee's name.
Conclusion
The SQL ORDER BY clause helps us arrange query results in a specific order. We can use it to sort numbers, text, dates, and calculated values in ascending or descending order. It can also be combined with other SQL clauses to filter, group, and organize data based on our requirements.