Most IT folks know the SQL ORDER BY clause forwards and backwards. Like the name implies, it's used at the end of a SELECT statement to specify the order of the records returned by the query. For example, if I wanted to query a table called PERSON and sort the results in alphabetical order by last name, my query would look something like this:

SELECT firstname, lastname, age

FROM person

ORDER BY lastname

Works for numbers the same way, so if I wanted to list those folks by their age, I might change the last line to "ORDER BY age" instead. Want to reverse the order? Just add "DESC" to the end of that line (by default, SQL returns them in ascending order).

And if you want to sort by multiple columns, just add them on and separate them with commas. "ORDER BY lastname, age" will, for instance, sort the results primarily by last name. However, if I have two people with the same last name in my record set, those two people will be further ordered by age.

Like I said, the ORDER BY clause is pretty straight forward, and it's generally one of the first things people learn right after "SELECT * FROM TABLE."

But what if you wanted to sort the output differently? ORDER BY is great straight out of the box for simple ascending/descending sorting (whether alphabetical or numeric), but let's say you wanted to sort your results by some other criteria.

What if our PERSON table also had a NERDLINESS column that desribed just how nerdly that person was. And let's say that all the records in that column had a value of 'None,' 'Basic', 'More Than Average', and 'Uber', in order of least to greatest. If we wanted to return the results in that same order, the normal ORDER BY clause wouldn't work. Instead, if we tried to ORDER BY our nerdliness column, SQL would want to return them in either alphabetical or reverse alphabetical order. That doesn't cut it here, at least not by itself. To get the results we want, we need to use the CASE function.

The CASE function will look really familiar to most people with any kind of programming experience. You use it like you would the SWITCH or SELECT CASE functions in most programming languages, the ones you'd usually use in place of a bunch of IF-THEN-ELSE statements. In this case, though, we're just using it in the ORDER BY clause to give our records values that ORDER BY can sort normally. For our PEOPLE.NERDLINESS example, we might use something like this:

SELECT firstname, lastname, age, nerdliness

FROM person

ORDER BY lastname,

CASE nerdliness

WHEN 'None' Then 1

WHEN 'Basic' Then 2

WHEN 'More Than Average' Then 3

WHEN 'Uber' then 4

END

Like a one developer colleague of mine mentioned, you'd probably be better off designing your data ahead of time so that it could be sorted properly to begin with. In this case, I might have created a table called NERDLINESS_LEVEL with just two columns: an integer identity (maybe NERDLINESS_ID?) and the corresponding value. Assuming I populated the table in the right order (1, 'None', 2, 'Basic', etc.), I could insert the corresponding nerdliness number into the PERSON table instead, join it to my NERDLINESS_LEVEL table, then sort by that NERDLINESS_ID value instead.

In the spirit of giving credit where due, I should mention that I pilfered this idea from 'so many trails ... so little time,' a blog I stumbled on while looking to solve this exact problem for myself just today. He uses a slightly different syntax for the CASE statement, but both should work. Lots of other great stuff there, too. Definitely check it out.