Selecting rows ordered by some column and distinct on another
Quite a clear question 🙂 SELECT t1.* FROM purchases t1 LEFT JOIN purchases t2 ON t1.address_id = t2.address_id AND t1.purchased_at < t2.purchased_at WHERE t2.purchased_at IS NULL ORDER BY t1.purchased_at DESC And most likely a faster approach: SELECT t1.* FROM purchases t1 JOIN ( SELECT address_id, max(purchased_at) max_purchased_at FROM purchases GROUP BY address_id ) t2 ON … Read more