DISTINCT and CASE expressions

The last lesson of this module adds two tools that shape what a query returns. DISTINCT removes repeated rows, so “which cities do our customers live in?” lists each city once. CASE computes a value from a set of conditions, so a price can become a label such as “budget” or “premium”, and a status can get a custom sort order.

DISTINCT removes repeated rows

Put DISTINCT right after SELECT, and rows that are identical in every column of the result appear once:

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, state TEXT);
INSERT INTO customers VALUES
  (1, 'Asha',   'Pune',       'Maharashtra'),
  (2, 'Bilal',  'Hyderabad',  'Telangana'),
  (3, 'Chitra', 'Pune',       'Maharashtra'),
  (4, 'Dev',    'Bilaspur',   'Chhattisgarh'),
  (5, 'Esha',   'Bilaspur',   'Himachal Pradesh'),
  (6, 'Farhan', NULL,         NULL),        -- address not given
  (7, 'Gita',   NULL,         NULL),
  (8, 'Hari',   'Hyderabad',  'Telangana');

-- One row per city. The empty first row is NULL: two customers have
-- no city, and DISTINCT counts their NULLs as one value.
SELECT DISTINCT city FROM customers ORDER BY city;

-- One row per combination of city and state.
SELECT DISTINCT city, state FROM customers ORDER BY state, city;

┌───────────┐
│   city    │
├───────────┤
│           │
│ Bilaspur  │
│ Hyderabad │
│ Pune      │
└───────────┘
┌───────────┬──────────────────┐
│   city    │      state       │
├───────────┼──────────────────┤
│           │                  │
│ Bilaspur  │ Chhattisgarh     │
│ Bilaspur  │ Himachal Pradesh │
│ Pune      │ Maharashtra      │
│ Hyderabad │ Telangana        │
└───────────┴──────────────────┘

Six of the eight customers live in three named cities, and two have no city at all. The first query returns four rows: the three cities and one NULL, shown as the empty first row (NULL sorts first in SQLite). Although two NULLs are never equal in a comparison, DISTINCT treats them as the same value, and so do PostgreSQL and MySQL.

The second query asks for distinct pairs of city and state, and Bilaspur now appears twice: the data has two different places of that name, one in Chhattisgarh and one in Himachal Pradesh. DISTINCT always applies to the whole row of the result: SELECT DISTINCT city, state cannot be told to compare only the cities. When you want one row per city but other columns too, the question is really “which row of each group?”, which the last section of this lesson answers.

Two habits help. First, when a query returns duplicates you did not expect, find out why before adding DISTINCT: in later modules the cause is often a join that multiplies rows, and DISTINCT only hides it. Second, DISTINCT is not free: to find the repeats the database has to compare the rows with each other, which takes time and memory on a large result.

CASE: a value chosen by conditions

A searched CASE lists conditions and the value to return for each:

CASE WHEN condition1 THEN value1 WHEN condition2 THEN value2 ELSE other END

The database tries the WHEN conditions from the top. The first one that is true gives the result, and the rest are not even looked at. If none is true, the result is the ELSE value, or NULL when there is no ELSE. A CASE is an expression like any other, so it can appear in the SELECT list, in WHERE, or in ORDER BY.

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price_paise INTEGER);
INSERT INTO products VALUES
  (1, 'Curry leaves', 1500), (2, 'Toor dal 1 kg', 16500),
  (3, 'Ghee 1 l', 65000),    (4, 'Kokum 200 g', NULL);   -- not priced yet

-- The WHEN conditions are tried from the top; the first true one wins.
-- A NULL price makes every comparison unknown, so ELSE catches it.
SELECT name, price_paise,
       CASE
         WHEN price_paise < 10000 THEN 'budget'
         WHEN price_paise < 50000 THEN 'mid'
         ELSE 'premium'
       END AS band
FROM products;

-- Fixed: test for NULL first. Without an ELSE, no match gives NULL.
SELECT name,
       CASE
         WHEN price_paise IS NULL THEN 'not priced'
         WHEN price_paise < 10000 THEN 'budget'
         WHEN price_paise < 50000 THEN 'mid'
         ELSE 'premium'
       END AS band,
       quote(CASE WHEN price_paise >= 50000 THEN 'yes' END) AS premium_only
FROM products;

┌───────────────┬─────────────┬─────────┐
│     name      │ price_paise │  band   │
├───────────────┼─────────────┼─────────┤
│ Curry leaves  │ 1500        │ budget  │
│ Toor dal 1 kg │ 16500       │ mid     │
│ Ghee 1 l      │ 65000       │ premium │
│ Kokum 200 g   │             │ premium │
└───────────────┴─────────────┴─────────┘
┌───────────────┬────────────┬──────────────┐
│     name      │    band    │ premium_only │
├───────────────┼────────────┼──────────────┤
│ Curry leaves  │ budget     │ NULL         │
│ Toor dal 1 kg │ mid        │ NULL         │
│ Ghee 1 l      │ premium    │ 'yes'        │
│ Kokum 200 g   │ not priced │ NULL         │
└───────────────┴────────────┴──────────────┘

The first query has a quiet bug: kokum, which has no price yet, is labelled “premium”. Both comparisons are unknown for a NULL, neither WHEN is true, and ELSE takes everything that is left. The second query tests for NULL first. Its last column shows a CASE without ELSE: every product that is not premium gets NULL.

The order of the WHENs matters

Because the first true condition wins, a broad condition placed above a narrow one hides it. This is the most common CASE bug:

CREATE TABLE orders (id INTEGER PRIMARY KEY, total_paise INTEGER NOT NULL);
INSERT INTO orders VALUES (1, 30000), (2, 80000), (3, 250000);

SELECT id, total_paise,
       -- Wrong: every total above 200000 is also above 50000,
       -- so the first WHEN catches it and 'large' is never reached.
       CASE
         WHEN total_paise > 50000  THEN 'medium'
         WHEN total_paise > 200000 THEN 'large'
         ELSE 'small'
       END AS wrong_order,
       -- Right: the narrowest condition first.
       CASE
         WHEN total_paise > 200000 THEN 'large'
         WHEN total_paise > 50000  THEN 'medium'
         ELSE 'small'
       END AS right_order
FROM orders;

┌────┬─────────────┬─────────────┬─────────────┐
│ id │ total_paise │ wrong_order │ right_order │
├────┼─────────────┼─────────────┼─────────────┤
│ 1  │ 30000       │ small       │ small       │
│ 2  │ 80000       │ medium      │ medium      │
│ 3  │ 250000      │ medium      │ large       │
└────┴─────────────┴─────────────┴─────────────┘

The order of 250000 paise is above 200000, but it is also above 50000, and in the first CASE that test comes first, so “large” is never reached. Either put the narrowest condition first, as in the second CASE, or use bands that cannot overlap, such as < 50000, then < 200000, then ELSE, as in the price bands above.

The simple form, and its trap

When every condition compares the same expression with a fixed value, the simple CASE is shorter: CASE status WHEN 'paid' THEN … WHEN 'shipped' THEN … END. It compares with =, and that has a consequence you know from the NULL lesson:

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT);
INSERT INTO orders VALUES (1, 'paid'), (2, 'shipped'), (3, NULL), (4, 'cancelled');

SELECT id,
       -- The simple form compares status with each value using =.
       CASE status
         WHEN 'paid'    THEN 'in progress'
         WHEN 'shipped' THEN 'in progress'
         WHEN NULL      THEN 'no status'   -- never matches: NULL = NULL is unknown
         ELSE 'closed'
       END AS simple_form,
       -- The searched form can test for NULL.
       CASE
         WHEN status IS NULL THEN 'no status'
         WHEN status IN ('paid', 'shipped') THEN 'in progress'
         ELSE 'closed'
       END AS searched_form,
       -- SQLite's shorthand for a two-way CASE.
       iif(status = 'cancelled', 'refund due', '-') AS with_iif
FROM orders;

┌────┬─────────────┬───────────────┬────────────┐
│ id │ simple_form │ searched_form │  with_iif  │
├────┼─────────────┼───────────────┼────────────┤
│ 1  │ in progress │ in progress   │ -          │
│ 2  │ in progress │ in progress   │ -          │
│ 3  │ closed      │ no status     │ -          │
│ 4  │ closed      │ closed        │ refund due │
└────┴─────────────┴───────────────┴────────────┘

Order 3 has no status, yet WHEN NULL did not match it: the simple form asks whether status = NULL, which is unknown, so the row fell through to ELSE. The searched form can say WHEN status IS NULL, and it can also use IN to give two statuses the same label. The last column uses iif(), SQLite’s shorthand for a two-way CASE. MySQL has IF(condition, then, else) for the same purpose, and PostgreSQL has neither, so a CASE is the version that runs everywhere.

One more portability rule: PostgreSQL requires the values a CASE can return to be convertible to a single data type, so CASE WHEN … THEN 1 ELSE 'none' END is an error there, while MySQL and SQLite accept it.

CASE in ORDER BY

Sorting by a CASE gives any order you need, such as the order in which an order moves through the shop:

CREATE TABLE orders (id INTEGER PRIMARY KEY, status TEXT NOT NULL);
INSERT INTO orders VALUES
  (1, 'shipped'), (2, 'pending'), (3, 'delivered'), (4, 'paid'), (5, 'pending'), (6, 'shipped');

-- Sort by the order in which an order moves through the shop, not alphabetically.
SELECT id, status
FROM orders
ORDER BY CASE status
           WHEN 'pending'   THEN 1
           WHEN 'paid'      THEN 2
           WHEN 'shipped'   THEN 3
           WHEN 'delivered' THEN 4
         END,
         id;

┌────┬───────────┐
│ id │  status   │
├────┼───────────┤
│ 2  │ pending   │
│ 5  │ pending   │
│ 4  │ paid      │
│ 1  │ shipped   │
│ 6  │ shipped   │
│ 3  │ delivered │
└────┴───────────┘

Alphabetical order would put “delivered” first; the CASE turns each status into its position in the process. id is the tie-breaker, as the previous lesson recommends.

One row per key: DISTINCT ON in PostgreSQL

A frequent question is “the latest order of each customer”. DISTINCT cannot answer it, because it compares whole rows. PostgreSQL has its own extension of the standard for exactly this: SELECT DISTINCT ON (customer) … keeps the first row of each group of rows that share the expressions in brackets, where “first” is decided by ORDER BY. The ORDER BY must start with the same expressions as DISTINCT ON, and the keys after them choose the row that wins, such as placed_at DESC for the newest. Without an ORDER BY, PostgreSQL’s manual warns, which row is first is unpredictable.

DISTINCT ON is not available in SQLite or MySQL. Here is what SQLite says to it:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, placed_at TEXT NOT NULL);
INSERT INTO orders VALUES
  (1, 'Asha', '2026-03-02 10:00'), (2, 'Bilal', '2026-03-03 12:30'),
  (3, 'Asha', '2026-03-09 19:15'), (4, 'Bilal', '2026-03-01 08:45');

-- PostgreSQL's DISTINCT ON: the first row of each customer in the ORDER BY.
-- SQLite (like MySQL) does not have it.
SELECT DISTINCT ON (customer) customer, id, placed_at
FROM orders
ORDER BY customer, placed_at DESC;

Parse error near line 8: near "ON": syntax error

In PostgreSQL, the same query returns one row per customer: order 3 for Asha and order 2 for Bilal, their latest. To get that result in every engine, number the rows of each customer with the window function row_number() and keep the first:

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL, placed_at TEXT NOT NULL);
INSERT INTO orders VALUES
  (1, 'Asha', '2026-03-02 10:00'), (2, 'Bilal', '2026-03-03 12:30'),
  (3, 'Asha', '2026-03-09 19:15'), (4, 'Bilal', '2026-03-01 08:45');

-- The latest order of each customer, in a form that PostgreSQL, MySQL 8
-- and SQLite all accept: number each customer's orders, newest first,
-- and keep number 1. (Window functions have a module of their own.)
SELECT customer, id, placed_at
FROM (
  SELECT customer, id, placed_at,
         row_number() OVER (PARTITION BY customer ORDER BY placed_at DESC, id DESC) AS newest
  FROM orders
) AS numbered
WHERE newest = 1
ORDER BY customer;

┌──────────┬────┬──────────────────┐
│ customer │ id │    placed_at     │
├──────────┼────┼──────────────────┤
│ Asha     │ 3  │ 2026-03-09 19:15 │
│ Bilal    │ 2  │ 2026-03-03 12:30 │
└──────────┴────┴──────────────────┘

PARTITION BY customer restarts the numbering for each customer, and ORDER BY placed_at DESC, id DESC gives number 1 to the newest order, with id deciding if two orders share a time. This form works in PostgreSQL, in MySQL 8.4 and in SQLite 3.25 and newer. MySQL insists on a name for a subquery in FROM, which is why the query calls it numbered. Window functions, including this “top-N per group” pattern, get a module of their own.

Key takeaways

• DISTINCT removes rows that are equal in every selected column and treats all NULLs as one value.
• A searched CASE tries its WHEN conditions from the top and returns the first match; without ELSE, no match gives NULL.
• Put the narrowest condition first, and test IS NULL before comparisons, or ELSE will catch missing values.
• The simple CASE x WHEN … compares with =, so WHEN NULL never matches.
• DISTINCT ON (PostgreSQL only) keeps the first row of each group by ORDER BY; row_number() does the same in every engine.