Sorting Data

You can sort data in your queries by using the ORDER BY clause. The most common construction is to use the name of a column to specify the desired order. Refer to the following example. The code in this section can be accessed from \Ch07\Samples09.sql in the sample files.

 SELECT SalesOrderId,OrderDate,SalesOrderNumber,CustomerID,Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader WHERE CustomerID=676 ORDER BY OrderDate 

The query above will return a list of orders from customer 676, arranged by the date of the order in an ascending fashion, with the oldest order listed first. If you want the orders with the most recent dates listed first, you can use the DESC clause, as shown below.

 SELECT SalesOrderId,OrderDate,SalesOrderNumber,CustomerID, Subtotal,TaxAmt,TotalDue FROM Sales.SalesOrderHeader WHERE CustomerID=676 ORDER BY OrderDate DESC 

The query above will return the SalesOrderNumber SO67260 in the first row because that is the most recent order.

The previous examples are ordered by date. You can also order your query by different datatypes, such as numbers and strings, but you cannot order your query by XML. Refer to the following example:

 SELECT * FROM Sales.Individual WHERE ModifiedDate BETWEEN '20041018' AND '20041020' ORDER BY Demographics 

If you execute the query above on the AdventureWorks database, you will receive the following message:

 Msg 305, Level 16, State 1, Line 1 The xml data type cannot be compared or sorted, except when using the IS NULL operator. 

The xml, text, ntext, or image datatypes cannot be used in the ORDER BY clause.

You may need to order your query by data that must be calculated first. Queries can use aliases to specify the name of calculated columns . You can also use this name when specifying the order, as shown below.

 SELECT SalesOrderId,OrderDate,SalesOrderNumber,CustomerID,        Subtotal+TaxAmt+Freight as Total FROM Sales.SalesOrderHeader WHERE CustomerID=676 ORDER BY Total DESC 

In some cases, ordering by only one column is not enough to specify the desired order. Suppose that you need to obtain the sales of customers 676 and 677. You need the sales ordered by Total, but you do not want to merge the sales of different customers. The following statement solves the problem.

 SELECT SalesOrderId,OrderDate,SalesOrderNumber,CustomerID,        Subtotal+TaxAmt+Freight as Total FROM sales.SalesOrderHeader WHERE CustomerID in (676 ,677) ORDER BY CustomerID,Total DESC 

In other situations, the desired order depends on a parameter. The following example demonstrates how to solve this issue.

 DECLARE @orderby varchar(10) SET @orderby='date' SELECT SalesOrderId,OrderDate,SalesOrderNumber,CustomerID,        Subtotal+TaxAmt+Freight as Total FROM sales.SalesOrderHeader WHERE CustomerID in (676 ,677) ORDER BY case @orderby WHEN 'date' THEN SalesOrderHeader.OrderDate                        WHEN 'total' THEN 5                        ELSE SalesOrderHeader.SalesorderId             END 

Note that the name total has been replaced by the number 5. This occurred because the use of aliases is not permitted when they are inside of CASE statements. You can therefore replace the alias with the order number of the column (5, in this case).

Caution 

When using CASE statements to specify varying orders, be sure that performance is not compromised. A discussion of performance is not within the scope of this chapter, but by viewing the estimated execution plan in SQL Server Management Studio, you can discover whether using a CASE statement will negatively impact performance.