AI Hints
Homeโ€บSQL Tutorialโ€บSorting Results
SQL ยท Lesson 18 of 126

Unique Values with DISTINCT

Remove duplicates.

โฑ 2 min readโ–ถ Video at 25:29๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

Helen asks, which genres do we sell? If you select the genre column, you get thirty rows, with Mystery, Thriller, and the rest repeated over and over. She wants each genre just once.

Unique Values with DISTINCT
From the video: Unique Values with DISTINCT
  • Which genres do we sell?
  • Thirty rows, lots of repeats
  • Each one just once

DISTINCT

Put distinct right after select. It removes repeated rows, so each value appears only once. We also sort the result, so it's easy to read. Run it. Nine genres, each listed once.

query.sqlsql
SELECT DISTINCT genre
FROM books
ORDER BY genre;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚   genre    โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ Business   โ”‚
โ”‚ Cooking    โ”‚
โ”‚ Fantasy    โ”‚
โ”‚ History    โ”‚
โ”‚ Mystery    โ”‚
โ”‚ Romance    โ”‚
โ”‚ Self-help  โ”‚
โ”‚ Technology โ”‚
โ”‚ Thriller   โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Exploring a column

Distinct is great for exploring a column you don't know yet. What values can an order's status have? Run it. Four values. Delivered, shipped, returned, and cancelled. Now you know exactly what to type in a where test.

query.sqlsql
SELECT DISTINCT status
FROM orders;
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚  status   โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ delivered โ”‚
โ”‚ shipped   โ”‚
โ”‚ returned  โ”‚
โ”‚ cancelled โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Summary

Distinct shows each value once. It's one of the first things to try on a new table. Next, the classic top N question, and how to get the next page of results.

Key takeaways
  • DISTINCT removes repeats
  • Great for exploring
  • Sort it to read it easily
Next up: Top N and Paging โ€” the newest, the next three