Complex/Conditional ORDER BYs

1

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.

One thought on “Complex/Conditional ORDER BYs

  1. […] Kenneth Fisher shows various methods for conditional ordering: […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 2,134 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: