DISTINCT

---

---

DISTINCT ON (The "Pick One" Logic, unique to Postgres)

While a standard DISTINCT removes duplicate rows based on every column in the SELECT list, DISTINCT ON (expression) allows you to keep only the first row of a specific group.

• Crucial Rule: You must use an ORDER BY clause that matches the DISTINCT ON columns to ensure you know which "first" row you are getting.

• Best for: Finding the "latest log entry per user" or the "most expensive product per category."

Example: To find the most recent order for every customer:

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

Here, Postgres groups by customer_id and, thanks to the DESC sort, grabs the topmost (newest) record for each.

---