Ordering the Result Set

Microsoft Jet SQL uses the ORDER BY clause to present the selected records in the order you designate:

SELECT Products.* 
FROM Products 
ORDER BY UnitPrice DESC, ProductName;

In this example, records are retrieved in descending order by unit price. All records that match a given price are put in ascending order based on the values in the ProductName field. Although it’s legal to use the ASC reserved word with an ORDER BY clause, it’s not required. If ASC is omitted, the default ascending order is used.

If an ORDER BY clause is used in combination with a WHERE clause, it follows the WHERE clause:

SELECT Orders.* 
FROM Orders 
WHERE Orders.OrderDate >= #4/1/96# 
ORDER BY Orders.EmployeeID;

The ORDER BY clause can contain an expression as well as a field in the underlying table, as in this example:

SELECT 
	Orders.OrderID, 
	Orders.RequiredDate,   
	IIf(ShipCountry = 'USA',RequiredDate+5,RequiredDate+10) 
		AS PastDueDate
FROM Orders
ORDER BY 
	IIf(ShipCountry = 'USA',RequiredDate+5,RequiredDate+10);

It’s also possible to specify the sort fields by their field positions rather than by their names, as in the following example:

SELECT 
	Employees.EmployeeID, 
	Employees.LastName, 
	Employees.FirstName
FROM Employees
ORDER BY 3, 1; 

This SELECT statement orders the query output by the third and first fields. In this case, the sort fields are the FirstName and EmployeeID fields. This feature is also useful with UNION queries, which are discussed later in this chapter.