April 4, 2016 by Kenneth Fisher
Typically it’s considered best to run your queries without an ORDER BY and let the application do any sorting needed. Sometimes though it’s faster and easier to let SQL handle the sorting for you. And that’s fine. SQL is very good at sorting.
Simple sorts are pretty easy. In the ORDER BY clause you can list out the columns by name or by position in the field list and determine if you want them sorted ascending or descending. But how about going beyond that?
Every now and again you need a fairly complex sort order. Say for example you want to order your data differently depending on the Status (say DueDate if the Status is 1, 2 or 3 and ShipDate otherwise). But you also want anyone with a TotalDue greater than $5000 sorted separately from those with a TotalDue less than $5000.
We actually have a couple of options for creating really complex orders.
- You can create calculated columns in the FieldList and order by them (see the link above). That requires those additional columns be returned by your query though and that isn’t always what you want.
- You can also use a correlated subquery as part of your ORDER BY, but honestly I’ve never needed to and I’d be a bit worried about performance.
- What I have used fairly frequently over the years is CASE statements. So for the above requirements you might get something like this:
SELECT DueDate, ShipDate, Status, TotalDue FROM Sales.SalesOrderHeader ORDER BY CASE WHEN TotalDue < 5000 THEN 1 ELSE 0 END, CASE WHEN Status IN (1,2,3) THEN DueDate ELSE ShipDate END
The first CASE statement in the ORDER BY breaks everything up into two groups. Those greater than or equal to 5000 and those less than 5000. The second CASE statement sorts by DueDate when the Status is a 1, 2 or 3 and by ShipDate otherwise.
You can see how you can combine CASE statements, columns and calculated columns in the field list to make very complex ORDER BYs.