Skip to content
Advertisement

Need to select 2 most recent dates for 2 id’s

I need to return 2 most recent dates for each client id, i have 12345 and 112233 and for each of them i need to create a function which supposed to return to me this

 id | client_id |       created       |    log
----+-----------+---------------------+------------
  1 |     12345 | 2019-05-02 12:01:10 | SOMETEXT1
  2 |     12345 | 2019-05-02 12:09:01 | SOMETEXT11
  3 |    112233 | 2019-05-01 13:10:12 | SOMETEXT2
  4 |    112233 | 2019-05-02 09:45:50 | SOMETEXT23
(4 rows)

Supposed to return this

>  id | client_id |       created       |    log
> ----+-----------+---------------------+------------   
    4 |    112233 | 2019-05-02 09:45:50 | SOMETEXT23   
    2 |     12345 | 2019-05-02 12:09:01 | SOMETEXT11 
(2 rows)

Advertisement

Answer

The best way in Postgres is distinct on:

select distinct on (client_id) t.*
from t
order by client_id, created desc;
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement