SQLite - DISTINCT Keyword

SQLite - The DISTINCT Keyword

The DISTINCT keyword is used with a SELECT statement to remove duplicate rows from a result set, returning only a single instance of each unique row.

This is especially useful when you are interested in the unique values present in a column or a combination of columns, rather than every single occurrence.

Syntax

The basic syntax for DISTINCT is to place it immediately after the SELECT keyword.

SELECT DISTINCT column1, column2, ...
FROM table_name
WHERE [condition];

When multiple columns are specified, DISTINCT considers the combination of values in those columns to determine uniqueness.

Example

Consider a SALES table with the following records. Note the duplicate entries for department and region.

ID | DEPARTMENT  | REGION    | AMOUNT
---|-------------|-----------|--------
1  | Electronics | North     | 1200
2  | Clothing    | North     | 800
3  | Electronics | South     | 1500
4  | Clothing    | North     | 300
5  | Books       | West      | 450
6  | Electronics | North     | 1100
7  | Books       | West      | 200

First, a standard SELECT query on the DEPARTMENT column shows all entries, including duplicates.

sqlite> SELECT department FROM SALES;

DEPARTMENT
-----------
Electronics
Clothing
Electronics
Clothing
Books
Electronics
Books

Now, let’s use the DISTINCT keyword to find the unique departments that have made sales.

sqlite> SELECT DISTINCT department FROM SALES;

DEPARTMENT
-----------
Electronics
Clothing
Books

The result now shows a clean list of unique departments.

You can also apply DISTINCT to multiple columns. The following query returns the unique combinations of department and region.

sqlite> SELECT DISTINCT department, region FROM SALES;

DEPARTMENT  | REGION
------------|-------
Electronics | North
Clothing    | North
Electronics | South
Books       | West