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

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

Using Nested CASE Expressions in SQL Server

Note: This article is intended for developers who have a working knowledge of SQL Server’s CASE expression. If you do not, I reading this article from Microsoft; it gives a pretty good overview of the basics.

Applications are loaded with conditional logic. So it’s not a big surprise when you stumble across a scenario where one half of a conditional expression is the result of an entirely separate conditional expression. If you’re running into this on the backend of your application, then a nested case statement might be of use to you. Let’s take a look at a real world example.

Let’s say you’ve been asked to write a procedure to generate a report for a business unit within your company. Let’s assume that this unit has both a Status property and a Recommendation property, both of which are stored in the database and related through foreign keys. The requestor gives you the following matrix to use for determining what output to display on the report:

Status	Recommendation	Report Output
In Production	Any	In Production
In Test	Add	Add to Production Queue
In Test	Remove	Return to Development Queue
In Test	Remain	Remain in Test Queue
In Development	Add	Add to Testing Queue
In Development	Remain	Remain in Development Queue
Other		None

Now that we have our problem, here’s an example of how to use a nested case expression to create a solution:

Using a Nested Case Expression

And there you have it. This example is pretty quick and dirty but it should give you a good grasp of the basic concepts needed to use this technique.

A couple quick additional tips:

1. Do not attempt to derive a column name for any of the nested case statements as this will cause an error. If you wish to use a derived column name for the entire expression, do so at the end of the outermost case statement (as I have done in the example).
2. You may nest deeper than two levels but, if you find the need arising, you may want to look at alternative approaches for maintainability’s sake before proceeding with a nesting nightmare.

Happy coding!

-Patrick