SQL Academy · Lesson

CASE in ORDER BY and Aggregates

Conditional sorting and counting.

Sorting with Custom Priority

SQL's ORDER BY clause normally sorts rows by a column's natural value. But you can embed a CASE expression inside ORDER BY to create a completely custom sort order — one that no single column could produce on its own.

This technique is powerful when business rules determine priority rather than raw data values.

SELECT product_name, status
FROM products
ORDER BY
  CASE status
    WHEN 'urgent'   THEN 1
    WHEN 'active'   THEN 2
    WHEN 'pending'  THEN 3
    ELSE                 4
  END;

How CASE Inside ORDER BY Works

When the database evaluates ORDER BY CASE ... END, it computes an integer (or any comparable value) for every row. Rows are then sorted by that computed value instead of — or in addition to — a raw column.

The CASE expression is not stored; it exists only for the duration of the query.

SELECT order_id, priority
FROM orders
ORDER BY
  CASE priority
    WHEN 'high'   THEN 1
    WHEN 'medium' THEN 2
    WHEN 'low'    THEN 3
    ELSE               9
  END,
  order_id;

All lessons in this course

← Back to SQL Academy