distinct-on postgresql sql sql-order-by

PostgreSQL DISTINCT ON with different ORDER BY


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?


  • Your order clause has purchased_at not address_id.Can you make your question clear.

    – Teja

    Mar 20, 2012 at 22:01

  • 2

    my order has purchase because i want it, but postgres also asks for address(see error message).

    – sl_bug

    Mar 20, 2012 at 22:03

  • 3

    Fully answered here –… Thanks to

    – sl_bug

    Dec 21, 2012 at 23:40

  • 2

    Personally I think requiring DISTINCT ON to match ORDER BY is very questionable, as there are a variety of legitimate use cases for having them differ. There is a post on postgresql.uservoice trying to change this for those who feel similarly.…

    – semicolon

    Jul 16, 2019 at 21:37

  • got the exact same issue, and facing the same limtation. At the moment I have broken it into a sub-query and then ordering, but it feels dirty.

    – Guy Park

    Oct 24, 2019 at 6:30


Documentation says:

DISTINCT ON ( expression [, …] ) keeps only the first row of each set of rows where the given expressions evaluate to equal. […] Note that the “first row” of each set is unpredictable unless ORDER BY is used to ensure that the desired row appears first. […] The DISTINCT ON expression(s) must match the leftmost ORDER BY expression(s).

Official documentation

So you’ll have to add the address_id to the order by.

Alternatively, if you’re looking for the full row that contains the most recent purchased product for each address_id and that result sorted by purchased_at then you’re trying to solve a greatest N per group problem which can be solved by the following approaches:

The general solution that should work in most DBMSs:

SELECT t1.* FROM purchases t1
    SELECT address_id, max(purchased_at) max_purchased_at
    FROM purchases
    WHERE product_id = 1
    GROUP BY address_id
) t2
ON t1.address_id = t2.address_id AND t1.purchased_at = t2.max_purchased_at
ORDER BY t1.purchased_at DESC

A more PostgreSQL-oriented solution based on @hkf’s answer:

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

Problem clarified, extended and solved here: Selecting rows ordered by some column and distinct on another


  • 49

    It works, but gives wrong ordering. That’s why i want to get rid of address_id in order clause

    – sl_bug

    Mar 20, 2012 at 22:12

  • 3

    But may be there is another way to select latest purchases for disticnt addresses?

    – sl_bug

    Mar 20, 2012 at 22:19

  • 1

    If you need to order by purchases.purchased_at, you can add purchased_at to your DISTINCT conditions: SELECT DISTINCT ON (purchases.purchased_at, address_id). However, two records with the same address_id but different purchased_at values will result in duplicates in the returned set. Make sure you are cognizant of the data you’re querying.

    Jan 26, 2015 at 21:44

  • 41

    The spirit of the question is clear. No need to pick on semantics. It’s sad that the accepted and most voted answer doesn’t help you solve the problem.

    – ichigolas

    Apr 7, 2017 at 22:39

  • 5

    Here is a postgresql.uservoice post trying to lift this limitation for those that agree it is a questionable limitation.…

    – semicolon

    Jul 16, 2019 at 21:40


A subquery can solve it:

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

Leading expressions in ORDER BY have to agree with columns in DISTINCT ON, so you can’t order by different columns in the same SELECT.

Only use an additional ORDER BY in the subquery if you want to pick a particular row from each set:

    SELECT DISTINCT ON (address_id) *
    FROM   purchases
    WHERE  product_id = 1
    ORDER  BY address_id, purchased_at DESC  -- get "latest" row per address_id
    ) p
ORDER  BY purchased_at DESC;

If purchased_at can be NULL, use DESC NULLS LAST – and match your index for best performance. See:

Related, with more explanation:


  • You cannot use DISTINCT ON without a matching ORDER BY. The first query requires an ORDER BY address_id inside the subquery.

    Jul 12, 2017 at 18:46

  • 7

    @AristotlePagaltzis: But you can. Wherever you got that from, it’s incorrect. You can use DISTINCT ON without ORDER BY in the same query. You get an arbitrary row from each set of peers defined by the DISTINCT ON clause in this case. Try it or follow the links above for details and links to the manual. ORDER BY in the same query (the same SELECT) just cannot disagree with DISTINCT ON. I did explain that, too.

    Jul 13, 2017 at 0:08

  • Huh, you’re right. I was blind to the implication of the “unpredictable unless ORDER BY is used” note in the docs because it does not make sense to me that the feature is implemented to be able deal with non-consecutive sets of values… yet won’t allow you to exploit that with an explicit ordering. Annoying.

    Jul 13, 2017 at 6:31

  • @AristotlePagaltzis: That’s because, internally, Postgres uses one of (at least) two distinct algorithms: either traverse a sorted list or work with hash values – whichever promises to be faster. In the later case the result is not sorted by DISTINCT ON expressions (yet).

    Jul 13, 2017 at 15:15

  • 1

    Thanks a lot! Your second query solved my issue and returns results in expected order!

    May 8, 2020 at 18:28


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

    (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


  • 3

    But this will be slower than just one query, no?

    – sl_bug

    Mar 20, 2012 at 22:05

  • 3

    Very marginally yes. Although since you have a purchases.* in your original select, I don’t think this is production code?

    – hkf

    Mar 20, 2012 at 22:06

  • 12

    I’d add that for newer versions of postgres you need to alias the subquery. For example: SELECT * FROM (SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM “purchases” WHERE “purchases”.”product_id” = 1 ORDER BY address_id DESC ) AS tmp ORDER BY tmp.purchased_at DESC

    – aembke

    Jun 17, 2014 at 20:38

  • 2

    This would return address_id twice (without need). Many clients have problems with duplicate column names. ORDER BY address_id DESC is pointless and misleading. It does nothing useful in this query. The result is an arbitrary pick from each set of rows with the same address_id, not the row with the latest purchased_at. The ambiguous question did not ask for that explicitly, but that’s almost certainly the OP’s intention. In short: do not use this query. I posted alternatives with explanation.

    Jul 17, 2017 at 15:22

  • Worked for me. Great answer.

    – Matt West

    Apr 24, 2020 at 23:31