ORDER BY Clause Overview
The ORDER BY clause is used to sort a result set based on one or more columns in ascending or descending order.
Syntax
The basic syntax of the ORDER BY clause is as follows:
SELECT column1, column2, ...
FROM table
ORDER BY column1 [ASC|DESC], column2 [ASC|DESC], ...;
column1,column2, ...: the columns on which the sorting is done[ASC|DESC]: optional parameter to specify the sorting order (ascending or descending), default is ascending
Example
Let us consider a table called "employees" with the following data:
| id | name | age | salary |
|---|---|---|---|
| 1 | Alice | 25 | 50000 |
| 2 | Bob | 30 | 60000 |
| 3 | Charlie | 35 | 70000 |
| 4 | David | 40 | 80000 |
| 5 | Eric | 45 | 90000 |
Here are some examples of using the ORDER BY clause:
-- sort by salary in ascending order
SELECT * FROM employees ORDER BY salary ASC;
-- sort by salary in descending order
SELECT * FROM employees ORDER BY salary DESC;
-- sort by age in ascending order, then by salary in descending order
SELECT * FROM employees ORDER BY age ASC, salary DESC;
Output
For the first example above, the output will be:
| id | name | age | salary |
|---|---|---|---|
| 1 | Alice | 25 | 50000 |
| 2 | Bob | 30 | 60000 |
| 3 | Charlie | 35 | 70000 |
| 4 | David | 40 | 80000 |
| 5 | Eric | 45 | 90000 |
For the second example above, the output will be:
| id | name | age | salary |
|---|---|---|---|
| 5 | Eric | 45 | 90000 |
| 4 | David | 40 | 80000 |
| 3 | Charlie | 35 | 70000 |
| 2 | Bob | 30 | 60000 |
| 1 | Alice | 25 | 50000 |
For the third example above, the output will be:
| id | name | age | salary |
|---|---|---|---|
| 1 | Alice | 25 | 50000 |
| 2 | Bob | 30 | 60000 |
| 3 | Charlie | 35 | 70000 |
| 4 | David | 40 | 80000 |
| 5 | Eric | 45 | 90000 |
Explanation
The ORDER BY clause works by finding the columns specified in the SELECT statement and sorting the rows in the result set based on those columns. If no sorting order is specified, the default is ascending order.
The ORDER BY clause can sort by multiple columns by including multiple columns separated by commas. If the columns are of different data types, the database will attempt to implicitly convert them to a common data type before sorting.
Use
The ORDER BY clause is most commonly used in SELECT statements to sort the resulting rows. It can also be used in subqueries, views, and with aggregate functions like MIN, MAX, COUNT, etc.
Important Points
- The ORDER BY clause must come after the FROM clause in a SELECT statement.
- The ORDER BY clause can sort by multiple columns by including them separated by commas.
- The columns specified in the ORDER BY clause must be part of the SELECT statement.
- By default, the sorting order is ascending, but it can be changed to descending using the "DESC" keyword.
Summary
The ORDER BY clause is a powerful tool that allows you to sort the resulting rows of a SELECT statement in ascending or descending order based on one or more columns. It is a commonly used clause in SQL and can also be used with aggregates and subqueries.