Shaping results: DISTINCT, LIMIT, LIKE, CASE

WHERE decides which rows come back. This lesson is about shaping them: dropping duplicates, keeping only the top few, matching text by pattern, filling in missing values and adding labels. None of these change the data. They change what the answer looks like.

DISTINCT removes duplicate rows. SELECT DISTINCT country FROM customers gives 7 countries, not 8, because two customers live in the UK. It applies to the whole row, not just the first column: SELECT DISTINCT customer_id, status keeps one row for each different pair, so a customer with paid and refunded orders appears twice.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 3;

-- name                price
-- Rain Shell Jacket   180.0
-- Trail Runner Shoes  120.0
-- Trekking Poles      90.0

LIMIT without ORDER BY takes whichever rows the database finds first, and that order can change between runs. Always sort first. LIMIT 3 OFFSET 3 skips three rows and returns the next three, which is how pages of results work.

LIKE matches text against a pattern. % stands for any run of characters (including none) and _ for exactly one. WHERE name LIKE 'T%' finds names starting with T: Trail Runner Shoes and Trekking Poles. '%Socks%' finds names containing Socks anywhere. SQLite ignores case for plain letters in LIKE, but PostgreSQL does not, so don't rely on it.

COALESCE returns the first of its arguments that isn't NULL. COALESCE(city, 'Unknown') shows Emma's missing city as Unknown and leaves everyone else's alone. It's a display fix, not a data fix: the city is still missing in the table.

SELECT name, price,
       CASE
         WHEN price >= 100 THEN 'premium'
         WHEN price >= 50  THEN 'mid'
         ELSE 'budget'
       END AS tier
FROM products;

CASE checks its WHEN conditions from top to bottom and stops at the first one that is true. So order matters: put the narrowest condition first. A row that matches no WHEN gets the ELSE value, and if there is no ELSE, it gets NULL.

SELECT name, price,
       CASE
         WHEN price >= 50  THEN 'mid'
         WHEN price >= 100 THEN 'premium'
         ELSE 'budget'
       END AS tier
FROM products;

Loading your workspace…