Skip to Content

How does DISTINCT ON clean up duplicates without GROUP BY gymnastics?

Category: SQL for AI Engineering

Answer

DISTINCT ON (col1, col2) keeps the FIRST row per (col1, col2) tuple after the ORDER BY. It’s a Postgres superpower for “give me one row per X, picked by some order”. Avoids correlated subqueries and window tricks.

Concrete examples from the fca project context

Example 1

SELECT DISTINCT ON (customer_id) customer_id, total, ts FROM orders ORDER BY customer_id, ts DESC -> latest order per customer.

Example 2

Picking the highest-scored embedding per user: order by user_id, score desc.

Data flow / flow chart

rows -> sort -> DISTINCT ON keeps first per tuple
  (order by determines "first")

Takeaway

DISTINCT ON is the cleanest Postgres idiom for “latest per group”. One CTE, sorted, done.