SQL: ORDER BY

In all the queries we've seen so far, the rows in the result table have not been ordered in any way. SQL just retrieved the rows in the order in which it found them in the table. 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 Order By clause to impose an order on the query results. The Order By keyword can only be used in Select statements.

The syntax for an Order By statement is as follows:

SELECT column_name FROM derived_name [WHERE condition] ORDER BY column_name [ASC, DESC]

The Where clause is optional. If a Where keyword presents in SQL statement, it comes before Order By clause. ASC/DESC means that the result will be displayed in ascending/descending order. If ASC/DESC value is omitted, the default is ASC.

Example #1

Select * From EmployeeAddressTable Order by FirstName ASC

Retrieve all records from EmployeeAddressTable table listed in alphabetical order.

Example #2

Select * From EmployeeStatisticsTable Order By Salary ASC, Benefits Desc

This will return all records sorted by the salary field in ascending order, with a secondary sort by benefits field in descending order.

Example #3

SELECT State,LastName,FirstName FROM EmployeeAddressTable WHERE State = 'Ohio' OR State = 'New York' ORDER BY State, LastName

The preceding example returns records listed primarily in order of state (Ohio then New York), and then for each state the relevant names are also listed in alphabetical order.