Postgresql how to select columns where it matches conditions?

Question

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?

Answer 1

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.