Order By clause
Introduction to ORDER BY clause
The SQL ORDER BY clause is used to sort the result-set in ascending or descending order. It sorts the records in your result set by one or more columns. This clause is typically used in conjunction with SELECT statement.
Syntax of ORDER BY Clause
Here is how you can use the ORDER BY clause:
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
- ASC stands for ascending and DESC stands for descending order.
- If you omit ASC|DESC, the ORDER BY clause uses ASC by default.
Example Uses of ORDER BY
Let's say you have a users table and it looks like this:
| id | name | age | |
|---|---|---|---|
| 1 | Alice | [email protected] | 25 |
| 2 | Bob | [email protected] | 23 |
| 3 | Charlie | [email protected] | 24 |
| 4 | David | [email protected] | 22 |
If you want to sort the users based on their age in ascending order, you would use the ORDER BY clause like so:
SELECT * FROM users ORDER BY age ASC;
The result set would look like this:
| id | name | age | |
|---|---|---|---|
| 4 | David | [email protected] | 22 |
| 2 | Bob | [email protected] | 23 |
| 3 | Charlie | [email protected] | 24 |
| 1 | Alice | [email protected] | 25 |
Alternatively, if you want to get the names of the users sorted in descending order, you would use the ORDER BY clause like so:
SELECT name FROM users ORDER BY name DESC;
The result set would look like this:
| name |
|---|
| David |
| Charlie |
| Bob |
| Alice |
Sorting By Multiple Columns
You can also sort by multiple columns. The result set is first sorted by the first column and then that sorted result set is sorted by the second column, and so on. For example:
SELECT * FROM users ORDER BY age ASC, name DESC;
This will first sort the users by age in ascending order, and then sort users of the same age by their name in descending order.
Conclusion
The ORDER BY clause is a powerful feature in SQL that allows us to sort our result set by one or more columns. We can use it to sort our results in ascending or descending order. By understanding how to use the ORDER BY clause, we can extract meaningful information from our database in a format that meets our needs.