CODEWITHSUNDEEP
sqlpostgresqlgreatest-n-per-group
Rrobinvip
Rrobinvip

Reputation: 119

Postgresql how to select columns where it matches conditions?

I have a table like this:

inventory_id | customer_id |         max         
--------------+-------------+---------------------
         4497 |           1 | 2005-07-28 00:00:00
         1449 |           1 | 2005-08-22 00:00:00
         1440 |           1 | 2005-08-02 00:00:00
         3232 |           1 | 2005-08-02 00:00:00
         3418 |           2 | 2005-08-02 00:00:00
          654 |           2 | 2005-08-02 00:00:00
         3164 |           2 | 2005-08-21 00:00:00
         2053 |           2 | 2005-07-27 00:00:00

I want to select rows where most recent date with corresponding columns, This is what I want to achieve:

inventory_id | customer_id |         max         
--------------+-------------+---------------------
         1449 |           1 | 2005-08-22 00:00:00
         3164 |           2 | 2005-08-21 00:00:00

I tried to use aggregate but I need inventory_id and customer_id appear at the same time. Is there any method that could do this?

Upvotes: 1

Views: 759

Answers (2)

CleanBold
CleanBold

Reputation: 1602

    SELECT inventory_id, customer_id, max FROM 
    (SELECT inventory_id, customer_id, max, 
    ROW_NUMBER() OVER(PARTITION BY customer_id ORDER BY max DESC) AS ROWNO 
    FROM inventory_table) AS A
    WHERE ROWNO=1

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

Use distinct on:

select distinct on (customer_id) t.*
from t
order by customer_id, max desc;

distinct on is a Postgres extension that returns on row per whatever is in the parentheses. This row is based on the order by -- the first one that appears in the sorted set.

Upvotes: 1

Related Questions