Using a CASE expression in the ORDER BY clause is a technique that allows us to obtain custom sequencing when the...
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?
Dig Deeper on Oracle development languages
Related Q&A from Rudy Limeback
Read an example of an SQL case expression from our SQL expert Rudy Limeback. Continue Reading
Read SQL expert Rudy Limeback's advice for counting combinations in a table with SQL's GROUP BY clause Continue Reading
Read about the Mimer Validator, a tool used to verify your SQL code, in this tip from SQL expert Rudy Limeback. Continue Reading