SQL ORDER BY

The ORDER BY command in SQL sorts the result set in either ascending or descending order. ORDER BY usually appears last in a SQL statement because it is performed after the result set has been retrieved.

Syntax

The syntax for the ORDER BY statement is as follows:

SELECT "column_name"
FROM "table_name"
[WHERE "condition"]
ORDER BY "column_name" [ASC, DESC];

The [ ] means that the WHERE statement is optional. However, if a WHERE clause exists, it comes before the ORDER BY clause. ASC means that the results will be shown in ascending order, and DESC means that the results will be shown in descending order. If neither is specified, the default is ASC.

It is possible to order by more than one column. For the case where we sort by two columns, the ORDER BY clause above becomes

ORDER BY "column_name1" [ASC, DESC], "column_name2" [ASC, DESC]

Assuming that we choose ascending order for both columns, the output will be ordered in ascending order according to column 1. If there is a tie for the value of column 1, we then sort in ascending order by column 2.

There is no limit to the number of columns you can use in the ORDER BY clause. However the general rule of thumb is you should only include the columns that are necessary for your use case.

Examples

We use the following table for Examples 1-3.

Table Store_Information

Example 1: ORDER BY a single column using column name

To list the contents of Table Store_Information by Sales in descending order, we key in,

SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;

Result:

Example 2: ORDER BY a single column using column position

In addition to column name, we may also use column position (based on the SQL query) to indicate which column we want to apply the ORDER BY clause. The first column is 1, second column is 2, and so on. In the above example, we will achieve the same results by the following command:

SELECT Store_Name, Sales, Txn_Date
FROM Store_Information
ORDER BY 2 DESC;

Example 3: ORDER BY a single column using a column not in the SELECT statement

The column(s) we use to sort the result do not need to be in the SELECT clause. For example, the following SQL,

SELECT Store_Name
FROM Store_Information
ORDER BY Sales DESC;

works fine and will give the following result:

Example 4: ORDER BY an expression

It is also possible to sort the result by an expression. For example, in the following table,

Table Product_Sales

we can use the SQL statement below to order the results by Revenue (defined as Price * Units):

SELECT Product_ID, Price*Units Revenue
FROM Product_Sales
ORDER BY Price*Units DESC;

Result:

Exercises

For these exercises, assume we have a table called User_Sales with the following data:

Table User_Sales

1. Which of the following SQL statement is valid? (There can be more than one answer)
a) SELECT * FROM User_Sales ORDER BY Sales;
b) SELECT * FROM User_Sales ORDER BY Last_Name DESC;
c) SELECT * FROM User_Sales ORDER BY First_Name WHERE Sales > 100;
d) SELECT * FROM User_Sales ORDER BY Last_Name, First_Name;

2. What is the result of the following query?
SELECT * FROM User_Sales WHERE Join_Date IN ('Apr-05-2015','Apr-15-2015') ORDER BY Sales;

3. What is the result of the following query?
SELECT * FROM User_Sales ORDER BY Join_Date DESC, Sales;