Ordering Query Output By Two Fields

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

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

Recent Articles: