CASE in ORDER BY

Part of the Beyond the Basics section of Coddy's SQL journey — lesson 2 of 27.

CASE isn't only for SELECT. You can put it inside ORDER BY to sort rows by a custom rule that the columns alone can't express.

SELECT name, status
FROM tickets
ORDER BY
    CASE status
        WHEN 'urgent' THEN 1
        WHEN 'high'   THEN 2
        WHEN 'normal' THEN 3
        ELSE 4
    END

Notice the shorter form: CASE column WHEN value THEN .... It's equivalent to WHEN column = value. Use whichever reads more cleanly.

Challenge

Easy

Available tables and columns:
tasks: id, title, priority

Return title and priority, ordered so that 'critical' comes first, then 'high', then 'low', and anything else last. Within the same priority, sort by title alphabetically.

Cheat sheet

Use CASE inside ORDER BY to sort by a custom rule:

SELECT name, status
FROM tickets
ORDER BY
    CASE status
        WHEN 'urgent' THEN 1
        WHEN 'high'   THEN 2
        WHEN 'normal' THEN 3
        ELSE 4
    END

The short form CASE column WHEN value THEN ... is equivalent to CASE WHEN column = value THEN ....

Try it yourself

SELECT title, priority
FROM tasks
ORDER BY
    -- ...

Test yourself

This lesson includes a short quiz. Start the lesson to answer it and track your progress.

All lessons in Beyond the Basics

1Conditional Logic

CASE WHENCASE in ORDER BYCOALESCE for NULLs NULLIF and IIF Recap - Attendance

4Set Operations

UNION vs UNION ALL INTERSECT EXCEPT Recap - Membership

2String Functions

LENGTH, UPPER, LOWER SUBSTR INSTR REPLACE and TRIM Concatenating with ||Recap - Invoices

5Recursive CTEs

A Number Sequence Walking a Hierarchy Recap - Org Chart

3Conditional Aggregation

COUNT with CASE SUM with CASE Pivoting Rows to Columns Recap - Traffic Sources

6Analytics Recipes

Top-N per Group Percentage of Total Running Total Keeping One Row per Key Recap - Top Customers