DISTINCT & DISTINCT ON

DISTINCT removes duplicate rows from a result set. It is standard SQL and works exactly the way you would expect once you have seen it. PostgreSQL also offers a genuinely useful extension, DISTINCT ON, that has no direct equivalent in the SQL standard.

SELECT DISTINCT

Every distinct city customers live in

SQL

SELECT DISTINCT city
FROM customers
ORDER BY city;

DISTINCT considers the entire row (every selected column) when deciding what counts as a duplicate. Selecting more columns means fewer rows collapse together.

Distinct combinations of city and country

SQL

SELECT DISTINCT city, country
FROM customers
ORDER BY country, city;

COUNT(DISTINCT column)

Combine DISTINCT with an aggregate to count how many unique values appear, rather than how many rows match.

How many distinct customers have placed an order?

SQL

SELECT COUNT(DISTINCT customer_id) AS customers_with_orders
FROM orders;

DISTINCT ON: one row per group, PostgreSQL-style

DISTINCT ON (columns) keeps only the first row for each distinct value of the given columns, based on whatever ORDER BY you provide. This makes “give me one representative row per group” queries trivial to write.

The most recent order for each customer

SQL

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, order_date, total_amount
FROM orders
ORDER BY customer_id, order_date DESC;

Reading this: PostgreSQL groups the rows conceptually by customer_id, sorts each group by order_date DESC, and keeps only the first row of each group — that is, the most recent order per customer, all in a single pass with no subquery or window function required.

ORDER BY must start with the DISTINCT ON columns

The ORDER BY clause has to begin with the same column(s) named in DISTINCT ON, in the same order, before any tie-breaking columns. PostgreSQL uses that leading part of ORDER BY to know how rows are grouped; it uses the rest to decide which row within each group “wins.” Omitting this or getting the order wrong produces confusing, effectively arbitrary results.

Why PostgreSQL developers love DISTINCT ON

Getting “the latest row per group” in standard SQL usually requires a window function (ROW_NUMBER() OVER (...) wrapped in a subquery) or a correlated subquery joined back to the original table. DISTINCT ON expresses the exact same intent in one flat, readable query — it is one of the most-loved PostgreSQL-only conveniences.

DISTINCT compares whole rows across every selected column
COUNT(DISTINCT col) counts unique values rather than rows
DISTINCT ON (col) keeps the first row per value of col, according to ORDER BY
DISTINCT ON is PostgreSQL-specific — not portable to other databases