Lessons · SQL · one row per value

Each value once

SELECT DISTINCT col returns each different value once, however many rows carry it.

Hone is a place to practise programming. This is one of its lessons, written out in full and free to read without an account.

What it is for

Which cities do we ship to, which statuses exist, which years have films: questions about the set of values, not the rows.

How to think about it

Ask whether you want rows or values. Values once: DISTINCT. How many of each: GROUP BY with COUNT instead. DISTINCT over several columns keeps each distinct combination.

Worked example

SELECT DISTINCT city FROM customers

Each city once.

ORDER BY city;

Sorted, so the list is readable.

SELECT COUNT(DISTINCT city) FROM customers;

How many different cities: the count of the set.

Your turn

Every status an order can have.

SELECT  status FROM orders;

The trap

DISTINCT applies to the whole row, not the first column. SELECT DISTINCT city, name gives each city-name pair once, which is nearly every row.

Practise one row per value on HoneA question on it now, a coding challenge where there is one, and it is remembered for review. Free, no email needed.