sql - 需要为 2 个 ID 选择 2 个最近的日期

Question

我需要为每个客户 ID 返回 2 个最近的日期，我有 12345 和 112233，我需要为他们中的每一个创建一个函数，该函数应该返回给我这个

 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)

应该归还这个

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

Answer 1

Postgres 中最好的方法是distinct on:

select distinct on (client_id) t.*
from t
order by client_id, created desc;