PostgreSQL DISTINCT ON with different ORDER BY

by

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
JOIN (
    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 * FROM (
  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

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. ORDER BY the IN value list
  3. Optimize query with OFFSET on large table
  4. sql ORDER BY multiple values in specific order?
  5. Sorting null values after all others, except special
  6. Sort NULL values to the end of a table
  7. PostgreSQL ORDER BY issue – natural sort
  8. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  9. Alphanumeric sorting with PostgreSQL
  10. Select first row in each GROUP BY group?
  11. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  12. How to use RETURNING with ON CONFLICT in PostgreSQL?
  13. Difference between language sql and language plpgsql in PostgreSQL functions
  14. When no ‘Order by’ is specified, what order does a query choose for your record set?
  15. How to include “zero” / “0” results in COUNT aggregate?
  16. delete “column does not exist”
  17. How to use Oracle ORDER BY and ROWNUM correctly?
  18. How to create a temporary function in PostgreSQL?
  19. Polymorphism in SQL database tables?
  20. How to round an average to 2 decimal places in PostgreSQL?
  21. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  22. postgresql: INSERT INTO … (SELECT * …)
  23. Sorting string column containing numbers in SQL?
  24. INSERT COMMAND :: ERROR: column “value” does not exist
  25. Return multiple columns and rows from a function PostgreSQL instead of record
  26. Postgres error updating column data
  27. SQL LIKE condition to check for integer?
  28. PostgreSQL how to create a copy of a database or schema?
  29. PostgreSQL: fill NULL values in timeserie query with previous value
  30. Get most common value for each value of another column in SQL

Leave a Comment