SQL ORDER BY

SELECT employee_id, first_name, last_name
FROM sto_employees
ORDER BY last_name ASC
LIMIT 7;

Structure of ORDER BY clause

SELECT col1, col2, ...
FROM table_name
ORDER BY column1 [ASC | DESC], column2 [ASC | DESC], ...;

Where:

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:

Query:

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:

Query:

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

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:

Query 1:

SELECT emp_name, emp_salary FROM sto_employees

ORDER BY 1,2 DESC;

Query 2:

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.