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_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
San Diego250Jan-07-1999
San Francisco300Jan-08-1999
Boston700Jan-08-1999

we key in,

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

Result:

Store_NameSalesTxn_Date
Los Angeles1500Jan-05-1999
Boston700Jan-08-1999
San Francisco300Jan-08-1999
San Diego250Jan-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_IDPriceUnits
1109
2154
3253

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

Next: SQL GROUP BY




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