SQL order by | order by Command in SQL - sql - sql tutorial - learn sql
- The ORDER BY command in SQL will sort 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.
- The ORDER BY keyword will sort the records in ascending order by default.
- To sort the records in descending order, use the DESC keyword.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSyntax:
Syntax:
- 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.
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExamples:
Examples:
- We use the following table for Examples 1-3.
Table Store_Information
| 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 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 1: ORDER BY a single column using column name
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 Name, Sales, Txn_Date
FROM Store_Information
ORDER BY Sales DESC;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialResult:
Result:
| 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 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 2: ORDER BY a single column using column position
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 Name, Sales, Txn_Date
FROM Store_Information
ORDER BY 2 DESC;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 3: ORDER BY a single column using a column not in the SELECT statement
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 Name
FROM Store_Information
ORDER BY Sales DESC;works fine and will give the following result:
| Name |
|---|
| Los Angeles |
| Boston |
| San Francisco |
| San Diego |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialExample 4: ORDER BY an expression
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;
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialResult:
Result:
| Product_ID | Revenue |
|---|---|
| 1 | 90 |
| 3 | 75 |
| 2 | 60 |
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Order by Sample Application 1
SQL Order by Sample Application 1
sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorialSQL Order by Sample Application 2
SQL Order by Sample Application 2
