The ORDER BY clause in SQL for sorting results

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.

  • For sorting the result 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, however, the order by must be used after the GROUP BY clause (See the last example).

An example of ORDER BY for sorting alphabetically

The first SQL ORDER BY query shows sorting the results of employees 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:

SQL order_by

You can see, even 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:

SQL order_by DESC

You see, 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:

SQL order_by date

Note: The NULL is considered lowest, so it appeared as the first result.

The example of using two columns in ORDER BY

The following query shows using multiple columns in ORDER BY SQL clause. The first is employee name and the second column specified is employee salary. No keyword for sorting is given so the default (ASC) will be taken as follows:

So, how this query worked?

The results are first sorted based on the first given column in the ORDER BY clause i.e. emp_name. For making 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 record with 3000 salary is displayed before the 4000.

As such, the ascending order sorts results from lowest to highest, so 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 columns 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:

SQL order_by two

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 ages. In that case, the age is used for only sorting the results. See the query and resultant table below:

SQL order_by column

The SQL ORDER BY using relative column position

In the ORDER BY clause, rather than using the column names, you may also use their relative position. For example, if the SELECT statement is as follows:

SELECT emp_name, emp_salary FROM sto_employees

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:

Query 2:

The table result sets for these two queries:

SQL order_by relative

In the second ORDER BY query, you can also see the Where clause is used before the ORDER BY clause.

Note: You may run these queries in the MS SQL Server as well as in the MY SQL database.

The example of using ORDER BY with GROUP BY clause

The following query shows using the ORDER BY clause with the GROUP BY clause. As mentioned earlier, you may use the ORDER BY clause after the GROUP BY.

For the demo, 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:

SQL order_by group-by

Using Having clause with ORDER BY

The last example shows using GROUP BY, HAVING and ORDER BY clause together for getting the sum of salaries for those employees that exceed 5000. The sorting is set to be based on employee names in descending order:

SQL order_by having

You can see, only three records are fetched this time.