SQL ORDER BY

SQL > SQL Commands > Order By

So far, we have seen how to get data out of a table using SELECT and WHERE commands. Often, however, we need to list the output in a particular order. This could be in ascending order, in descending order, or could be based on either numerical value or text value. In such cases, we can use the ORDER BY keyword to achieve our goal.

The syntax for an 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. In this case, 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.

For example, we may wish to list the contents of Table Store_Information by Sales, in descending order:

Table Store_Information

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
San Diego	250	Jan-07-1999
San Francisco	300	Jan-08-1999
Boston	700	Jan-08-1999

we key in,

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

Result:

Store_Name	Sales	Txn_Date
Los Angeles	1500	Jan-05-1999
Boston	700	Jan-08-1999
San Francisco	300	Jan-08-1999
San Diego	250	Jan-07-1999

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;

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:

Store_Name

Los Angeles

Boston

San Francisco

San Diego

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

Table Product_Sales

Product_ID	Price	Units
1	10	9
2	15	4
3	25	3

we can use the following SQL 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:

Product_ID	Revenue
1	90
3	75
2	60