ORDER BY Clause

Sorts records using specified criteria in ascending or descending order.

 

Syntax

 

ORDER BY expression [ASC | DESC] [, expression [ASC | DESC] ...]

 

The ORDER BY clause has these parts:

 

Part

Description

expression

An expression with which to sort records.

 

Remarks

 

Use the ORDER BY clause to sort records using one or more expressions. The expressions can be columns, operations on columns, or literals. If the ORDER BY clause includes more than one expression, the records are sorted by the value returned by the first expression, then the records with the same value of the first expression are sorted by the value returned by the second expression, and so on.

 

Use a numeric expression that is a single number to sort by the value of an output column with the given index (one-based).

 

Use the ASC and DESC keywords to specify whether the sorting is done in ascending (ASC) or descending (DESC) order. If neither ASC nor DESC is specified, the sorting is done in ascending order.

 

We can sort records using numeric, date, or string expressions, but not binary expressions.

 

Examples

 

This example uses the ORDER BY clause to sort employees by last name in ascending order:

 

SELECT * FROM [Employees] ORDER BY [Last Name] ASC;

 

The ASC keyword is optional, so we may rewrite the above example as:

 

SELECT * FROM [Employees] ORDER BY [Last Name];

 

This example uses the ORDER BY clause to sort employees by country, and then by city:

 

SELECT * FROM [Employees] ORDER BY [Country], [City];

 

This example uses the ORDER BY clause and the INNER JOIN operator to sort orders by shipper in ascending order, and then by the number of days it took to ship the order in descending order:

 

SELECT [Company Name], [Order Date], [Shipped Date] FROM [Orders] INNER JOIN [Shippers] ON [Shipper ID] = [Ship Via] ORDER BY [Company Name], DateDiff("d", [Shipped Date], [Order Date]) DESC;

 

This example is similar to the example above, but includes the number of days it took to ship the order as the output column, and uses references to the output columns in the ORDER BY clause:

 

SELECT [Company Name], [Order Date], [Shipped Date], DateDiff("d", [Shipped Date], [Order Date]) AS [Shipment Days] FROM [Orders] INNER JOIN [Shippers] ON [Shipper ID] = [Ship Via] ORDER BY 1, 4 DESC;