For an instructor lead, in-depth look at learning SQL click below.
In Structured Query Language (SQL), the ORDER BY clause is used to sort the result set in ascending or descending order. Originally, this is based on one or more columns. The ORDER BY clause can only be used in select statements. Using SELECT queries without the ORDER BY clause it may lead to different results depending upon the way the database structures data. It is usually a good practice to always use the ORDER BY clause to ensure the data you are working with is ordered consistently.
Understanding the Syntax:
Let’s look at the syntax of the ORDER BY clause in SQL:
sql
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Here ASC denotes ascending and DESC denotes descending order. If neither is specified, ASC is assumed by default.
Some examples of the ORDER BY clause:
Consider the following ‘Employees’ table:
| EmployeeID | FirstName | LastName | Salary |
|------------|-----------|-----------|--------|
| 1 | John | Doe | 5000 |
| 2 | Jane | Smith | 6000 |
| 3 | Jim | Brown | 5500 |
| 4 | Jake | Davis | 5200 |
Let’s fetch the data sorted by ‘FirstName’ using ORDER BY:
sql
SELECT * FROM Employees
ORDER BY FirstName ASC;
This will give you the following result, which is sorted by ‘FirstName’ in ascending order:
| EmployeeID | FirstName | LastName | Salary |
|------------|-----------|-----------|--------|
| 2 | Jane | Smith | 6000 |
| 4 | Jake | Davis | 5200 |
| 1 | John | Doe | 5000 |
| 3 | Jim | Brown | 5500 |
We can easily alter the query to change the sorting order by replacing ASC with DESC:
sql
SELECT * FROM Employees
ORDER BY FirstName DESC;
This will give you the following result, which is sorted by ‘FirstName’ in descending order:
| EmployeeID | FirstName | LastName | Salary |
|------------|-----------|-----------|--------|
| 3 | Jim | Brown | 5500 |
| 1 | John | Doe | 5000 |
| 4 | Jake | Davis | 5200 |
| 2 | Jane | Smith | 6000 |
In Conclusion:
The ORDER BY clause is very useful in SQL for providing a meaningful order to data returned from a SELECT query. It introduces a specific structure to data output and yields easier data interpretation and processing. It is a key aspect in SQL, data analytics and any other field that requires data sorting.