Alex Rivera | Logout

PostgreSQL DISTINCT ON with different ORDER BY

Asked 2012-03-20T21:59:36.757
359

I want to run this query:

SELECT DISTINCT ON (address_id) purchases.address_id, purchases.*
FROM purchases
WHERE purchases.product_id = 1
ORDER BY purchases.purchased_at DESC

But I get this error:

PG::Error: ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions

Adding address_id as first ORDER BY expression silences the error, but I really don't want to add sorting over address_id. Is it possible to do without ordering by address_id?

sql postgresql sql-order-by distinct-on
Edit
Report

1 Answer

74

You can order by address_id in an subquery, then order by what you want in an outer query.

SELECT * FROM 
    (SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* 
    FROM "purchases" 
    WHERE "purchases"."product_id" = 1 ORDER BY address_id DESC ) 
ORDER BY purchased_at DESC
answered 2012-03-20T22:04:33.793

Your Answer