What is the ORDER BY clause in SQL?
The ORDER BY clause in SQL is used to sort the results of a query in ascending or descending order.
You may return the complete sorted result set or optionally limit the rows by using the FETCH or OFFSET clauses.
SELECT employee_id, first_name, last_name FROM sto_employees ORDER BY last_name ASC LIMIT 7;
- For sorting the results in ascending order, use the ASC keyword. This is the default and returns the result from lowest to highest. For example, sorting the Employee Name column alphabetically (A to Z).
- To sort the results from highest to lowest e.g. Z to A or 100 to 1 etc., use the DESC clause with ORDER BY.
- You may specify one or more columns in the ORDER BY Clause.
- You may use the ORDER BY clause with the GROUP BY clause, however, the ORDER BY must be used after the GROUP BY clause (See the last example).
Structure of ORDER BY clause
SELECT col1, col2, ... FROM table_name ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;
|col1, col2…||The columns to be selected in the result set|
|table_name||Provide the table name from where you want to retrieve data.|
|ORDER BY||Specifies the sorting order.|
|ASC (default)||To sort results in Ascending order.|
|DESC||To sort results in Descending order.|
An example of ORDER BY for sorting alphabetically
The first SQL ORDER BY query shows sorting the results of the employee’s table.
We will simply sort the results alphabetically based on employee names. The results are displayed with and without using the ORDER BY clause:
SELECT * FROM sto_employees ORDER BY emp_name
You can see, even if no ASC keyword is used, the results are sorted alphabetically, as such this is the default sorting order.
Sorting results in descending order example
For sorting the results in descending order, use the DESC keyword as shown in the query below:
SELECT * FROM sto_employees ORDER BY emp_name DESC
You see that the results are sorted from highest to lowest i.e. Z-A.
Sorting the date column example
This query sorts the results in ascending order for the date column i.e. join_date in our example table. The results should be sorted from lowest to highest dates as follows:
SELECT * FROM sto_employees ORDER BY join_date
The example of using two columns in ORDER BY
The following query shows using multiple columns in the ORDER BY SQL clause. The first is employee’s name and the second column specified is the employee’s salary.
No keyword for sorting is given so the default (ASC) will be taken as follows:
SELECT * FROM sto_employees ORDER BY emp_name,emp_salary
So, how does this query work?
The results are first sorted based on the first given column in the ORDER BY clause i.e. emp_name. To make you understand, I entered the name “Jimmy” twice. One with 3000 and the other with 4000 salaries. In the Order By query, you can see the employee Jimmy’s record with 3000 salary is displayed before the 4000.
As such, the ascending order sorts results from lowest to highest, so the first records are sorted by employee names and as a match is found, the second specified column is checked and sorted.
You may also specify DESC for the second or first column as shown in the query below.
One ASC and other DESC sorting order example
For this multiple-column example, the first column (emp_name) uses the default sorting order i.e. ascending and the other is using the DESC order.
See the same table as used in the above example and compare the two tables:
SELECT * FROM sto_employees ORDER BY emp_name,emp_salary DESC
You see, the record of Jimmy with 4000 salary is appearing first in this query.
Using a column that is not specified in Select
You may also use a column in the ORDER BY clause that is not specified in the SELECT statement. For example, we only require the employee names and their salaries sorted by employee age.
In that case, the age is used for only sorting the results. See the query and resultant table below:
SELECT emp_name, emp_salary FROM sto_employees ORDER BY emp_age;
SQL ORDER BY using relative column position/index
In the ORDER BY clause, rather than using the column names, you may also use their relative position/column index. For example, if the SELECT statement is as follows:
Then in the ORDER BY clause, you may use the 1 for emp_name and 2 for the emp_salary column.
See the following two queries where relative positions are used in the ORDER BY clause:
SELECT emp_name, emp_salary FROM sto_employees ORDER BY 1,2 DESC;
SELECT * FROM sto_employees WHERE emp_age >=30 ORDER BY 4 DESC;
The table result sets for these two queries:
In the second ORDER BY query, you can also see the WHERE clause is used before the ORDER BY clause.
The example of using ORDER BY with GROUP BY clause
The following query shows using the ORDER BY clause with the SQL GROUP BY clause.
For the example, I used the sto_emp_salary_paid table that stores employee salaries along with their names. The aggregate SUM function is used to get the SUM of salaries for each employee and results are grouped and sorted by employee names as follows:
SELECT emp_name, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid GROUP BY emp_name ORDER BY emp_name;
Using HAVING clause with ORDER BY
The last example shows using GROUP BY, HAVING, and ORDER BY clauses together to get the sum of salaries for those employees that exceed 5000.
The sorting is set to be based on employee names in descending order:
SELECT emp_name, SUM(emp_sal_paid) As "Total Paid" FROM sto_emp_salary_paid GROUP BY emp_name HAVING SUM(emp_sal_paid) > 5000 ORDER BY emp_name DESC;
You can see that only three records are fetched this time.