CASE Expressions in ORDER BY

Overview

CASE isn't limited to SELECT: you can use it in ORDER BY to apply custom sorting logic that isn't a simple column order. Here we force the Engineering department to the top of the list regardless of salary, then sort the rest by pay.

The Query

Step-by-Step Breakdown

1. CASE WHEN d.name = 'Engineering' THEN 0 ELSE 1 END: assigns 0 to Engineering rows and 1 to all others, so Engineering sorts first.
2. e.salary DESC: the secondary sort, applied within each group.
3. The two-key ORDER BY produces Engineering (highest paid first), then everyone else (highest paid first).

Variations

Sort movies: Sci-Fi first, then newest:

Common Mistakes

• Mismatched THEN types. All THEN/ELSE branches must return the same data type or the CASE errors.
• Forgetting the secondary sort. A single CASE in ORDER BY leaves ties unsorted, add a second key.
• Assuming it changes values. CASE in ORDER BY only affects ordering, never the returned column values.