DISTINCT

Each value once with DISTINCT

Module 1 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

List the different values in a column, each once.

SELECT DISTINCT drops the repeats.

Next: One column, every row

On your own New data, same idea

List the authors in the library, each author once.

The database

9 rows

book_id	title	author	genre	year	pages
1	A Study in Scarlet	Arthur Conan Doyle	Mystery	1887	108
2	Charlie and the Chocolate Factory	Roald Dahl	Children	1964	155
3	Frankenstein	Mary Shelley	Horror	1818	280
4	Dracula	Bram Stoker	Horror	1897	418
5	Pride and Prejudice	Jane Austen	Romance	1813	432
6	The Hound of the Baskervilles	Arthur Conan Doyle	Mystery	1902	256
7	Emma	Jane Austen	Romance	1815	474
8	Matilda	Roald Dahl	Children	1988	240
9	The Da Vinci Code	Dan Brown	Thriller	2003	454

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How DISTINCT works

What does DISTINCT do?

SELECT DISTINCT genre FROM books;
It removes repeated rows from the result, so each different value comes back once.
Where does DISTINCT go?

Straight after SELECT, before the columns. It applies to the whole row, not just the first column.
What about two columns?

SELECT DISTINCT genre, author keeps every different pair of genre and author. Two rows only count as repeats when every column matches.
Is there another way?

GROUP BY genre gives the same list, and it's how you count per genre later (COUNT(*)). For a plain list, DISTINCT says what you mean.
How is this like Excel?

It's like the UNIQUE function: =UNIQUE(D2:D12). The original rows stay as they were (Remove Duplicates, by contrast, deletes them from the sheet).

Know someone fighting with a spreadsheet? Send them this page.