lesson 09 Calculations & data quality · beginner

Unique values with DISTINCT

Return one copy of each distinct value.

the idea

DISTINCT removes duplicate output rows. It applies to the whole set of selected columns, not just the first column. NULL is kept once.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

SELECT DISTINCT city
FROM customers;

Watch for: Adding id makes every row unique, so it defeats this task.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1. 1

   See duplicate cities first. Many customers share a city. The raw list repeats Chicago and Austin. DISTINCT will keep one row per distinct value.

   step-1.sql Run

   sql›

   SELECT city
   FROM customers
   ORDER BY city;

2. 2

   Keep each city once. DISTINCT removes duplicate output rows across the selected columns. NULL is kept once if it appears.

   step-2.sql Run

   sql›

   SELECT DISTINCT city
   FROM customers;