SQL ORDER BY

Summary: in this tutorial, you will learn how to use the SQL ORDER BY clause to sort the result set based on different criteria.

Introduction to SQL ORDER BY clause

To sort a result set returned by a SELECT statement, you use the  ORDER BY clause. The following query illustrates how to use the  ORDER BY clause in a SELECTstatement:

SELECT 
    select_list
FROM
    table_name
ORDER BY 
   (expr | column) ASC,
   (expr | column) DESC;Code language: SQL (Structured Query Language) (sql)

The  ORDER BY clause allows you to sort the result set by a column or an expression with the condition that the value in the column or the returned value of the expression must be sortable i.e., the data type of the result must be the character, numeric or date-time.

To sort a result set in ascending order, you use ASC keyword, and in descending order, you use the DESCkeyword. If you don’t specify any keyword explicitly, the  ORDER BY clause sorts the result set in ascending order by default.

To sort multiple columns, you need to specify additional columns in the  ORDER BY clause. You can sort by one column in ascending order and another column in descending order.

SQL ORDER BY examples

Let’s take a look at some examples of sorting result sets using the  ORDER BY clause.

SQL ORDER BY one column example

For example, you can sort all employees by the last name in ascending order as the following query:

SELECT 
    lastname, firstname
FROM
    employees
ORDER BY lastname;Code language: SQL (Structured Query Language) (sql)

SQL ORDER BY multiple columns example

You can sort the result set by multiple columns. The following query sorts employees by last name in descending order and first name in ascending order:

SELECT 
    lastname, firstname
FROM
    employees
ORDER BY lastname DESC , firstname ASC;Code language: SQL (Structured Query Language) (sql)

The database engine sorts the result set based on the last name in descending order first. And then it sorts the sorted result set by the first name in ascending order to produce the final result set.

SQL ORDER BY with expressions

The  ORDER BY clause can also accept expressions. For example, you can use the CONCATstring function to construct the full names of employees, and then sort the result set by the full name as the following query:

SELECT 
    CONCAT(lastname, ',', firstname) fullname
FROM
    employees
ORDER BY CONCAT(lastname, ',', firstname);Code language: SQL (Structured Query Language) (sql)

The column alias is used for formatting the output of the result set. You can use the column alias in the ORDER BY clause rather than the expression. The following query produces the same output:

SELECT 
    CONCAT(lastname, ',', firstname) fullname
FROM
    employees
ORDER BY CONCAT(lastname, ',', firstname);Code language: SQL (Structured Query Language) (sql)

SQL ORDER BY with positional number

The positional number is the position of the column in the SELECTclause. The position number starts with 1, 2, 3, etc. SQL allows you to use these positional numbers rather than columns or expressions to sort the result set.

The following statement sorts the employees by hired date in descending order to find the most junior employees in the company:

SELECT 
    lastname, firstname, DATE(hiredate)
FROM
    employees
ORDER BY 3 DESC;Code language: SQL (Structured Query Language) (sql)

SQL sorts the result set by hiredatecolumn, which has positional number 3.

The positional number that refers to a specific column is changed when you change the columns in the  SELECT clause. This may lead to an unexpected result if you forget to change the positional number. Therefore, it is not recommended to use the positional number in the  ORDER BY clause. You only use it if you have no choice.

In this tutorial, you’ve learned how to use the  ORDER BY clause to sort result sets returned by a SELECTstatement.