PostgreSQL DISTINCT ON with different ORDER BY

Question

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

User · Accepted Answer

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

User · Answer

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

User · Answer

You can also done this by using group by clause      SELECT purchases address id  purchases   FROM  purchases      WHERE  purchases   product id    1 GROUP BY address id  purchases purchased at ORDER purchases purchased at DESC

User · Answer

A subquery can solve it  SELECT   FROM        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   FROM        SELECT DISTINCT ON  address id        FROM   purchases     WHERE  product id   1     ORDER  BY address id  purchased at DESC  -- get  quot latest quot  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   Sort by column ASC  but NULL values first  Why does ORDER BY NULLS LAST affect the query plan on a primary key   Related  with more explanation   Select first row in each GROUP BY group  Sort by column ASC  but NULL values first

User · Answer

For anyone using Flask-SQLAlchemy  this worked for me  from app import db from app models import Purchases from sqlalchemy orm import aliased from sqlalchemy import desc  stmt   Purchases query distinct Purchases address id  subquery  purchases   alias   aliased Purchases  stmt  distinct   db session query alias  distinct order by desc alias purchased at

User · Answer

It can also be solved using the following query along with other answers  WITH purchase data AS           SELECT address id  purchased at  product id                  row number   OVER  PARTITION BY address id ORDER BY purchased at DESC  AS row number         FROM purchases         WHERE product id   1  SELECT address id  purchased at  product id FROM purchase data where row number   1

User · Answer

Window function may solve that in one pass   SELECT DISTINCT ON  address id      LAST VALUE purchases address id  OVER wnd AS address id FROM  purchases  WHERE  purchases   product id    1 WINDOW wnd AS      PARTITION BY address id ORDER BY purchases purchased at DESC    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING

[sql] PostgreSQL DISTINCT ON with different ORDER BY

Examples related to sql

Examples related to postgresql

Examples related to sql-order-by

Examples related to distinct-on