SQL ORDER BY allows you to sort the result based on one or more sort keys in ascending or descending fashion. Here is the syntax of SQL ORDER BY:
SELECT column1, column2…
FROM table_name
ORDER BY sort_key1 [ASC | DESC], sort_key2 [ASC | DESC] …
SQL ORDER BY can only be used in SQL SELECT statement. The sort_key (sort_key1, sort_key2,...) in SQL ORDER BY must be sortable. It can be character, numeric or date time data type. With SQL ORDER BY you can specify the sort order by using keyword ASC (means sort in ascending order) and DESC (means sort in descending order). If you don’t specify the sort order by using ASC and DESC keywords, the default ordering is ascending. You can either sort one or more columns in any sort order you want.
Let's take a look at several examples of using SQL ORDER BY:
For example, you can sort all employees by their last name by performing the following query. In this case you sort last name in ascending order.
SELECT lastname, firstname
FROM employees
ORDER BY lastname
lastname firstname
--------- ---------
Buchanan Steven
Callahan Laura
Davolio Nancy
Dodsworth Anne
Fuller Andrew
King Robert
Leverling Janet
Peacock Margaret
Suyama Michael
We can also sort the result based on last name in descending order and first name in ascending order by performing the following query:
SELECT lastname, firstname
FROM employees
ORDER BY lastname DESC, firstname ASC
lastname firstname
--------- ---------
Suyama Michael
Peacock Margaret
Leverling Janet
King Robert
Fuller Andrew
Dodsworth Anne
Davolio Nancy
Callahan Laura
Buchanan Steven
SQL sorts the result based on the last name in descending order first. And based on this sorted result, it then sorts the first name in ascending order.
SQL ORDER BY can also accept any expression. For example, you can use CONCAT function, which allows you concatenate multiple strings in into one, to construct full name of employees and then sort them all by full name. Here is the query example:
SELECT CONCAT(lastname,',',firstname) fullname
FROM employees
ORDER BY fullname
fullname
----------------
Buchanan,Steven
Callahan,Laura
Davolio,Nancy
Dodsworth,Anne
Fuller,Andrew
King,Robert
Leverling,Janet
Peacock,Margaret
Suyama,Michael
Almost RDMBS allows you to specify the sort key based on the positional number of column in the selection list. The starting position of column in the selection list is 1 and so on… and those positional numbers can be listed in SQL ORDER BY clause.
Suppose we want to sort employees by hired date to find out who are the most new hires people of the company, we can use positional number in ORDER BY clause as follows:
SELECT lastname, firstname, date(hiredate)
FROM employees
ORDER BY 3 DESC
lastname firstname date(hiredate)
--------- --------- --------------
Dodsworth Anne 1994-11-15
Callahan Laura 1994-03-05
King Robert 1994-01-02
Buchanan Steven 1993-10-17
Suyama Michael 1993-10-17
Peacock Margaret 1993-05-03
Fuller Andrew 1992-08-14
Davolio Nancy 1992-05-01
Leverling Janet 1992-04-01
SQL sorts the result by hiredate column which positional number of hiredate column in the selection list is 3.
Because the positional number is changed when you add more columns in the selection list so you have to change it also in the SQL ORDER BY clause. This sometimes led you to an unexpected result if you forget to change the positional number. Therefore it is not recommended to use positional number in ORDER BY clause, you only use it if you don't have any option.
In this tutorial, you’ve learnt how to use SQL ORDER BY clause to sort the result in ascending and descending order.