DISTINCT ​

DISTINCT removes duplicate rows from the projected query result. It applies to all selected expressions together, rather than to one column independently.

For duplicate elimination, NULL is equal to another NULL. Text equality follows the expression's collation. DISTINCT ON is not supported.

Syntax ​

SELECT DISTINCT expression [, ...]
FROM source

Examples ​

Unique combinations ​

The same country can occur more than once when the complete projected rows differ:

CREATE TABLE visits (country TEXT, device TEXT);
INSERT INTO visits VALUES ('GB', 'desktop'), ('GB', 'desktop'), ('GB', 'mobile'), ('FR', 'mobile');

SELECT DISTINCT country, device
FROM visits
ORDER BY country, device
FORMAT csv;

"FR","mobile"
"GB","desktop"
"GB","mobile"

Duplicate NULL values ​

CREATE TABLE readings (value INTEGER);
INSERT INTO readings VALUES (1), (1), (NULL), (NULL);

SELECT DISTINCT value
FROM readings
ORDER BY value
FORMAT csv;

\N
1

For a count of unique non-NULL values, use count(DISTINCT value).