PostgreSQL DISTINCT ON with different ORDER BY

PostgreSQL DISTINCT ON with different ORDER BY

Asked on January 4, 2019 in PostgreSQL.
Add Comment


  • 3 Answer(s)

    Below documentation says :

    DISTINCT ON ( expression [, …] ) get 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

    Because user will  have to includes  the address_id to the order by.

    Another , user where see  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:

    This is basic  solution for 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 most PostgreSQL-oriented answer 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
    

    solution is find, extended and solved here: Selecting rows ordered by some column and distinct on another

     

     

     

     

    Answered on January 4, 2019.
    Add Comment

    Below subquery can used to order by address_id,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 on January 4, 2019.
    Add Comment

    Below 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)
    

     

    Answered on January 4, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.