Selecting rows ordered by some column and distinct on another

Quite a clear question :)

SELECT t1.* FROM purchases t1LEFT JOIN purchases t2ON t1.address_id = t2.address_id AND t1.purchased_at < t2.purchased_atWHERE t2.purchased_at IS NULLORDER BY t1.purchased_at DESC

And most likely a faster approach:

SELECT t1.* FROM purchases t1JOIN (    SELECT address_id, max(purchased_at) max_purchased_at    FROM purchases    GROUP BY address_id) t2ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_atORDER BY t1.purchased_at DESC

Your ORDER BY is used by DISTINCT ON for picking which row for each distinct address_id to produce. If you then want to order the resulting records, make the DISTINCT ON a subselect and order its results:

SELECT * FROM(  SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*  FROM "purchases"  WHERE "purchases"."product_id" = 2  ORDER BY purchases.address_id ASC, purchases.purchased_at DESC) distinct_addrsorder by distinct_addrs.purchased_at DESC

This query is trickier to rephrase properly than it looks.

The currently accepted, join-based answer doesn’t correctly handle the case where two candidate rows have the same given purchased_at value: it will return both rows.

You can get the right behaviour this way:

SELECT * FROM purchases AS givenWHERE product_id = 2AND NOT EXISTS (    SELECT NULL FROM purchases AS other    WHERE given.address_id = other.address_id    AND (given.purchased_at < other.purchased_at OR given.id < other.id))ORDER BY purchased_at DESC

Note how it has a fallback of comparing id values to disambiguate the case in which the purchased_at values match. This ensures that the condition can only ever be true for a single row among those that have the same address_id value.

The original query using DISTINCT ON handles this case automatically!

Also note the way that you are forced to encode the fact that you want “the latest for each address_id” twice, both in the given.purchased_at < other.purchased_at condition and the ORDER BY purchased_at DESC clause, and you have to make sure they match. I had to spend a few extra minutes to convince myself that this query is really positively correct.

It’s much easier to write this query correctly and understandbly by using DISTINCT ON together with an outer subquery, as suggested by dbenhur.