The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.
- By default ORDER BY sorts the data in ascending order.
- We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.
Syntax of all ways of using ORDER BY is shown below:
Sort according to one column: To sort in ascending or descending order we can use the keywords ASC or DESC respectively.
Syntax:
SELECT * FROM table_name ORDER BY column_name ASC|DESC
table_name: name of the table.
column_name: name of the column according to which the data is needed to be arranged.
ASC: to sort the data in ascending order.
DESC: to sort the data in descending order.
| : use either ASC or DESC to sort in ascending or descending order
Sort according to multiple columns: To sort in ascending or descending order we can use the keywords ASC or DESC respectively. To sort according to multiple columns, separate the names of columns by (,) operator.
Syntax:
SELECT * FROM table_name ORDER BY column1 ASC|DESC , column2 ASC|DESC
NOTE: ORDER BY is not supported in SELECT/INTO or CREATE TABLE AS SELECT (CTAS) statements in Azure Synapse Analytics (SQL Data Warehouse) or Parallel Data Warehouse.
Examples in this section demonstrate the basic functionality of the ORDER BY clause using the minimum required syntax.
Specifying a single column defined in the select list
The following example orders the result set by the numeric ProductID column. Because a specific sort order is not specified, the default (ascending order) is used.
USE AdventureWorks2012;
GO
SELECT ProductID, Name FROM Production.Product
WHERE Name LIKE 'Lock Washer%'
ORDER BY ProductID;
Specifying a column that is not defined in the select list
The following example orders the result set by a column that is not included in the select list, but is defined in the table specified in the FROM clause.
USE AdventureWorks2012;
GO
SELECT ProductID, Name, Color
FROM Production.Product
ORDER BY ListPrice;
Specifying an alias as the sort column
The following example specifies the column alias SchemaName as the sort order column.
USE AdventureWorks2012;
GO
SELECT name, SCHEMA_NAME(schema_id) AS SchemaName
FROM sys.objects
WHERE type = 'U'
ORDER BY SchemaName;
Specifying an expression as the sort column:
The following example uses an expression as the sort column. The expression is defined by using the DATEPART function to sort the result set by the year in which employees were hired.
USE AdventureWorks2012;
GO
SELECT BusinessEntityID, JobTitle, HireDate
FROM HumanResources.Employee
ORDER BY DATEPART(year, HireDate);
