DISTINCT Keyword

The SELECT DISTINCT statement is used to return only unique (different) values from one or more columns. It eliminates duplicate rows from the result set.

DROP TABLE IF EXISTS Books;

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    Title VARCHAR(255),
    AuthorID INTEGER,
    Genre VARCHAR(50),
    PublishedYear INTEGER
);

INSERT INTO Books (BookID, Title, AuthorID, Genre, PublishedYear)
VALUES (1, 'The Hobbit', 101, 'Fantasy', 1937),
       (2, '1984', 102, 'Dystopian', 1949),
       (3, 'The Fellowship of the Ring', 101, 'Fantasy', 1954),
       (4, 'Dune', 103, 'Sci-Fi', 1965);

Database Overview & Sample Data

The database is pre-loaded with the Books table. Run the query below to explore the data.

Books Table

SELECT * FROM Books;

1. DISTINCT on a Single Column

Operation: Retrieve the unique genres from the Books table.

Query:

SELECT DISTINCT Genre
FROM Books;

Result:

2. Using COUNT with DISTINCT

You can use DISTINCT inside an aggregate function like COUNT() to count only the unique occurrences of a value.

Operation: Count how many different authors are represented in the Books table.

Query:

SELECT COUNT(DISTINCT AuthorID) AS NumberOfAuthors
FROM Books;

Explanation:

• A regular COUNT(AuthorID) would return 4, as there are four rows.
• COUNT(DISTINCT AuthorID) first finds the unique AuthorID values (101, 102, 103) and then counts them.

Result:

3. DISTINCT on Multiple Columns

When DISTINCT is applied to more than one column, it returns only the unique combinations of values from those columns.

Operation: Get the unique combinations of AuthorID and Genre.

Query:

SELECT DISTINCT AuthorID, Genre
FROM Books;

Explanation: The query looks at each row's (AuthorID, Genre) pair and eliminates any duplicates:

• Row 1: (101, Fantasy) - Unique, included.
• Row 2: (102, Dystopian) - Unique, included.
• Row 3: (101, Fantasy) - Duplicate of Row 1, excluded.
• Row 4: (103, Sci-Fi) - Unique, included.

Result:

Hands-on Exercise

Task: Retrieve the unique list of genres available in the Books table.

Your Query:

-- Write your query here

Expected Results: