Tables and Select

Case Labels

Tagging Rows by Priority

A CASE expression returns a different value per row based on conditions. It is the SQL way to label or bucket data.

Program

Play the script to label each task as high, medium, or low priority.

case_labels.sql
Replay: real traced execution (multi-file project)
CREATE TABLE tasks (name TEXT, priority INTEGER);
INSERT INTO tasks VALUES ('deploy', 5), ('docs', 2), ('cleanup', 0);
SELECT name, priority, CASE WHEN priority >= 3 THEN 'high' WHEN priority >= 1 THEN 'medium' ELSE 'low' END AS label FROM tasks ORDER BY priority DESC;

  1. tables ← 1 row

    1CREATE TABLE tasks (name TEXT, priority INTEGER);2INSERT INTO tasks VALUES ('deploy', 5), ('docs', 2), ('cleanup', 0);
    values this step1 rowtables

  2. tasks ← 3 rows

    1CREATE TABLE tasks (name TEXT, priority INTEGER);2INSERT INTO tasks VALUES ('deploy', 5), ('docs', 2), ('cleanup', 0);3SELECT name, priority, CASE WHEN priority >= 3 THEN 'high' WHEN priority >= 1 THEN 'medium' ELSE 'low' END AS label FROM tasks ORDER BY priority DESC;
    values this step3 rowstasks

  3. result ← 3 rows

    2INSERT INTO tasks VALUES ('deploy', 5), ('docs', 2), ('cleanup', 0);3SELECT name, priority, CASE WHEN priority >= 3 THEN 'high' WHEN priority >= 1 THEN 'medium' ELSE 'low' END AS label FROM tasks ORDER BY priority DESC;
    values this step3 rowsresult

Follow the Labels

  1. tasks starts with deploy priority 5, docs priority 2, and cleanup priority 0.
  2. deploy matches priority >= 3, so it gets high.
  3. docs misses the first check but matches priority >= 1, so it gets medium.
  4. cleanup matches neither check, so ELSE gives it low.
  5. ORDER BY priority DESC lists deploy, then docs, then cleanup. | name | priority | label | | --- | --- | --- | | deploy | 5 | high | | docs | 2 | medium | | cleanup | 0 | low |
CASE `CASE WHEN ... THEN ...` returns a value chosen per row.
first match wins Conditions are tested top to bottom; the first matching `WHEN` decides the label.
ELSE `ELSE 'low'` is the default when no `WHEN` matches.

Exercise: case_labels.sql

Reproduce the high, medium, and low labels, then change docs to priority 3 and predict its label before running it.