SQL “Order by” statement is used for sorting table records either in the ascending or in the descending order at the time of retrieval.
SQL ORDER BY Syntax
Select column1, column2,... columnN from table_name order by column1 ASC|DESC;
Above we have specified sorting on column1. Here, ASC is used for sorting in ascending order whereas DESC is used for sorting in descending order. If any sorting option is not specified, then the default sorting will be done in ascending order.
Sample Table
Employee
| ID | NAME | SALARY | ADDRESS | DESIGNATION | AGE |
|---|---|---|---|---|---|
| 1 | Peter Clark | 90000.00 | Daphne Road Manukau | Manager | 36 |
| 2 | Alfredo Smith | 60000.00 | Pimpama Drive Rotorua | Principal Engineer | 32 |
| 3 | James Cook | 40500.00 | Saint-Antoine Quebec | SDE I | 27 |
| 4 | Hudson Stewart | 45000.00 | Stoney Creek Ontario | SDE II | 30 |
| 5 | Nathan Taylor | 55000.00 | Long Street, Woolston | SDE II | 31 |
Example
- Fetching table records in ascending order of salary
SELECT * FROM Employee ORDER BY Salary;
| ID | NAME | SALARY | ADDRESS | DESIGNATION | AGE |
|---|---|---|---|---|---|
| 3 | James Cook | 40500.00 | Saint-Antoine Quebec | SDE I | 27 |
| 4 | Hudson Stewart | 45000.00 | Stoney Creek Ontario | SDE II | 30 |
| 5 | Nathan Taylor | 55000.00 | Long Street, Woolston | SDE II | 31 |
| 2 | Alfredo Smith | 60000.00 | Pimpama Drive Rotorua | Principal Engineer | 32 |
| 1 | Peter Clark | 90000.00 | Daphne Road Manukau | Manager | 36 |
- SQL ORDER BY query to fetch records in descending order of salary
SELECT * FROM Employee ORDER BY Salary DESC;
ORDER BY On Multi Columns
There will be instances when we need to apply sort on more than one column. In that case, we will need to define a multi-column sort order separated by a comma.
Syntax
Select * from table_name order by column1 ASC, column2 DESC;
Example
Fetching table records sorted on designation and salary. This means records will be sorted on designation first, and if designation matches, then the sorting will be done on salary.
SELECT * FROM Employee ORDER BY Designation ASC,Salary ASC;
This is all about the “Order By” statement in SQL.
Hope you liked the article. If you have any doubts or concerns, please feel free to write us in comments or mail us at admin@codekru.com