Order By Case and Derived Columns

Not too long ago, I learned how to combine SQL Server’s ORDER BY with CASE expressions. I was dazzled by my newfound ability to apply custom sorting instructions to my queries. It wasn’t too long afterwards that I attempted to use this newfound technique to order the results of a query containing a derived column. That’s when the error messages started pouring in. It didn’t seem to make sense as it is perfectly possible to use ORDER BY with derived columns when CASE isn’t involved. Well, after searching far and wide and can tell you with a comfortable degree of certainty that it cannot be done (I’ve tested it out in SQL Server 2000, 2005, and 2008 and the result is the same in all three).

There’s good news, however: A workaround exists.

All you have to do is create a second query that selects from the query containing the derived column and implement the ORDER BY for the second query. The result is slightly less elegant but it does the trick. I’ve used the Northwind database to create the following example:

How to use Order By and Case with Derived Columns
How to use Order By and Case with Derived Columns

The extra SELECT statement will probably result in a slight increase in performance cost but any effect on the end-user experience should be nominal.

So, that’s how it’s done. Hopefully I’ve saved you some time and energy.

If you know of any other approaches to this problem that I haven’t mentioned, or if you have any additional information on the issue itself, I’d love to hear from you.

Comments and feedback are always welcomed!

-Patrick

Advertisements

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