CASE expressions in the ORDER BY clause
SQL expert Rudy Limeback discusses using a CASE expression in the ORDER BY clause.
Using a CASE expression in the ORDER BY clause is a technique that allows us to obtain custom sequencing when the...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
natural values in the table columns are not themselves adequate for the task.
I'll give two examples.
The first example is for a situation where the Human Resources department wants to see a list of salaries by position, except that they want salespeople first, IT staff second, management third, and everybody else last, with of course the individuals within each group listed in descending order by salary.
The CASE expression is used to "translate" the positions into values that meet the sort sequence requirements.
SELECT position , empno , salary FROM personnel ORDER BY CASE WHEN position = 'sales' THEN 1 WHEN position = 'it' THEN 2 WHEN position = 'mgmt' THEN 3 ELSE 4 END , salary DESC
The CASE expression actually creates an additional separate column that is "appended" to the other columns extracted from the table. The CASE expression calculates the value of this additional column for each row. This additional column allows the result set to be sorted, but it is not returned in the result set (unless you also include the CASE expression in the SELECT clause). The CASE expression is needed because the position names do not sort into the right sequence, neither ASC nor DESC, on their own.
The second example is very similar:
SELECT position , empno , salary FROM personnel ORDER BY CASE WHEN position = 'sales' THEN 'Curly' WHEN position = 'it' THEN 'Larry' WHEN position = 'mgmt' THEN 'Moe' ELSE 'Shemp' END , salary DESC
Can you figure this one out?