Lecture 15: Finding Unique Values with DISTINCT

This lecture covers the DISTINCT keyword to find unique values.

Often, a column in your table will contain duplicate values. For example, in our `Contacts` table, we might have several people with the same first name, or several people who live at the same address.

If you want to get a list of all the first names in your table, but you only want to see each unique name once, you use the DISTINCT keyword.

Using `DISTINCT` on a Single Column

Let’s create a simple table to demonstrate this. Imagine a `Sales` table that logs every item sold.

CREATE TABLE Sales (
    SaleID INTEGER PRIMARY KEY,
    ItemName TEXT,
    Price REAL
);

INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.50);
INSERT INTO Sales (ItemName, Price) VALUES ('Banana', 0.30);
INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.50);
INSERT INTO Sales (ItemName, Price) VALUES ('Orange', 0.60);
INSERT INTO Sales (ItemName, Price) VALUES ('Banana', 0.30);
INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.50);

If we select the `ItemName` column, we get six results, with duplicates:

SELECT ItemName FROM Sales;
-- Result:
-- Apple
-- Banana
-- Apple
-- Orange
-- Banana
-- Apple

To get a clean list of only the unique items that were sold, we add the DISTINCT keyword right after SELECT.

SELECT DISTINCT ItemName FROM Sales;
-- Result:
-- Apple
-- Banana
-- Orange

This shows us every unique value in the `ItemName` column, with all duplicates removed.

Using `DISTINCT` on Multiple Columns

You can also use DISTINCT across multiple columns. When you do this, it returns rows where the *combination* of the selected columns is unique.

Let’s add another sale to our table where an Apple was sold at a different price.

INSERT INTO Sales (ItemName, Price) VALUES ('Apple', 0.55);

Now, let’s select the distinct combinations of `ItemName` and `Price`.

SELECT DISTINCT ItemName, Price FROM Sales;
-- Result:
-- Apple, 0.50
-- Banana, 0.30
-- Orange, 0.60
-- Apple, 0.55

Notice that ‘Apple, 0.50’ appears only once, even though it was in the table three times. The new combination of ‘Apple, 0.55’ is also included because it is a unique pairing of values.

In the Next Lecture…

We will start exploring the world of aggregate functions. These are powerful functions that perform a calculation on a set of rows and return a single summary value. We will begin with the most common one: COUNT(), which is used to count the number of rows that match a certain criteria.

Lecture 14: Limiting Your Results with LIMIT