SQL ORDER BY
Summary: In this tutorial, you will learn how to use SQL ORDER BY clause to sort the result set based on different criteria.
SQL ORDER BY statement allows you to sort the result set 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 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 in ascending order by performing the following query:
SELECT lastname, firstname
FROM employees
ORDER BY lastname
In the above example you sort the result based on a single column. You can also sort the result by multiple columns as well. This feature is known as multi columns sorting. For example you can sort employees by their last name in descending order and first name in ascending order.
SELECT lastname, firstname
FROM employees
ORDER BY lastname DESC, firstname ASC
SQL sorts the result set based on last name in descending order first. And then based on this sorted result, it sorts the first name in ascending order.
SQL ORDER BY can also accept expressions. For example, you can use CONCAT string function, which allows you to concatenate multiple strings in into one, to construct full name of employees and then sort them all by full name. The following query illustrates the idea:
SELECT CONCAT(lastname,',',firstname) fullname
FROM employees
ORDER BY fullname
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 count from 1, 2 and so on… and those positional numbers can be listed in SQL ORDER BY clause.
Suppose you want to sort employees by hired date to find out who are the most junior employees in the company; You can use positional number in ORDER BY clause as follows:
SELECT lastname, firstname, date(hiredate)
FROM employees
ORDER BY 3 DESC
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 learned how to use SQL ORDER BY clause to sort the result in ascending and descending order.
Related Tutorials