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 
1109
2154
3253

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
190
375
260

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.




Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact