8.4 CASE Inside ORDER BY (Custom Sort Order)

By Ravindra Bagale Last updated: 31 October 2026

Why. Sometimes High priority must sort before Low even if text order disagrees.

What. CASE in ORDER BY assigns a sort key number to each label.

How. ORDER BY CASE priority WHEN 'High' THEN 1 WHEN 'Medium' THEN 2 ELSE 3 END

Steps

Map High→1, Medium→2, Low→3, else 4.
ORDER BY that CASE.
Scan the result order.

Examples

Example 1 · dataset: interview_mini

SELECT ticket_id, priority, status
FROM tickets
ORDER BY CASE priority
  WHEN 'High' THEN 1
  WHEN 'Medium' THEN 2
  WHEN 'Low' THEN 3
  ELSE 4 END,
  ticket_id;

Output

ticket_id	priority	status
1	High	Open
4	High	Open
7	High	Closed
3	Medium	Open
8	Medium	Open
2	Low	Closed
5	Low	Closed
6		Open

Meaning. High tickets first, NULL priority last.

Ravindra Bagale's Tip

Custom sort with CASE is an interview favourite.