Unique Results with DISTINCT
Part of the First Queries section of Coddy's PostgreSQL journey. Lesson 12 of 20.
DISTINCT removes duplicate output rows. It applies to the full list of selected values, not to one chosen column. Filtering still happens before duplicate removal.
Input table products:
id | name | category | price | stock | active | note |
|---|---|---|---|---|---|---|
1 | Map | travel | 12 | 5 | True | NULL |
2 | Notebook | books | 8 | 0 | True | Recycled |
3 | Atlas | books | 24 | 3 | False | NULL |
4 | Mug | home | 12 | 8 | True | Blue |
5 | lamp | home | 30 | 2 | False | Clearance |
6 | Mini map | travel | 6 | 10 | True | NULL |
SELECT DISTINCT category FROM products ORDER BY category;The result lists books, home and travel once each even though several products belong to each category. Adding id would make every output pair different because every product has its own id.
SELECT DISTINCT returns each distinct combination of selected values once.
Challenge
EasyReturn each price used by an in-stock product exactly once. Name the column price and sort it ascending.
The input tables are shown in the test panel. Each run starts from fresh data. Return the named columns in the stated order; do not hard-code the displayed rows.
Try it yourself
-- Write your SQL here.
This lesson includes a short quiz. Start the lesson to answer it and track your progress.
All lessons in First Queries
3Build Useful Results
Calculate and Name ColumnsUnique Results with DISTINCTLabels with CASEFallbacks with COALESCEPacking LabelsPractice on your own: PostgreSQL playground