SQL ORDER BY Clause | Sort Data in SQL Queries

SQL > SQL Commands > 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

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

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:

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

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:

Store_Name

Los Angeles

Boston

San Francisco

San Diego

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

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

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:

Product_ID	Revenue
1	90
3	75
2	60

Exercises

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

Table User_Sales

First_Name	Last_Name	Gender	Join_Date	Sales
Sophie	Lee	F	Apr-05-2015	500
Richard	Brown	M	Apr-05-2015	200
Jamal	Santo	M	Apr-09-2015	350
Casey	Healy	M	Apr-09-2015	80
Jill	Wilkes	F	Apr-15-2015	210

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;

Next: SQL GROUP BY

This page was last updated on October 11, 2024.

SQL ORDER BY