Question:
I want to order the output of my query by two fields. Is this possible? That is, if two values are the same, I want the output ordered by a second field.
Answer:
The standard ORDER BY clause allows you to sort query output by one or more columns. For example, if you have a query to retrieve orders from the Northwind Orders table you can sort it by CustomerID and EmployeeID like this:
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM OrdersORDER BY CustomerID, EmployeeID
With this result:
OrderID CustomerID EmployeeID OrderDate ----------- ---------- ----------- --------------------------- 10835 ALFKI 1 1998-01-15 00:00:00.00010952 ALFKI 1 1998-03-16 00:00:00.00011011 ALFKI 3 1998-04-09 00:00:00.00010692 ALFKI 4 1997-10-03 00:00:00.00010702 ALFKI 4 1997-10-13 00:00:00.00010643 ALFKI 6 1997-08-25 00:00:00.00010625 ANATR 3 1997-08-08 00:00:00.00010759 ANATR 3 1997-11-28 00:00:00.000
By default each ORDER BY expression sorts the results in ascending order. But you can specify the direction of the sort for each expression separately like this:
SELECT OrderID, CustomerID, EmployeeID, OrderDate FROM OrdersORDER BY CustomerID ASC, EmployeeID DESC
With this result:
OrderID CustomerID EmployeeID OrderDate ----------- ---------- ----------- --------------------------- 10643 ALFKI 6 1997-08-25 00:00:00.00010692 ALFKI 4 1997-10-03 00:00:00.00010702 ALFKI 4 1997-10-13 00:00:00.00011011 ALFKI 3 1998-04-09 00:00:00.00010835 ALFKI 1 1998-01-15 00:00:00.00010952 ALFKI 1 1998-03-16 00:00:00.00010308 ANATR 7 1996-09-18 00:00:00.00010926 ANATR 4 1998-03-04 00:00:00.000