获取查询(Postgres)中给定的每个id的最大值

Question

我有这样的查询

select distinct on (foreign_id) foreign_id, id, date
from table
where foreign_id IN (1, 2, 3)

我得到的结果

<表类>foreign_idid日期tbody><<tr>11012019-03-2021022020-02-0631032020-06-09

您缺少一个ORDER BY:

select distinct on (foreign_id) foreign_id, id, date
from table
where foreign_id IN (1, 2, 3)
order by foreign_id, date DESC;

可以这样使用解析函数:

select select foreign_id, id, date from
(select foreign_id, id, date, 
row_number() over (partition by foreign_id order by date desc) as rn
from table
where foreign_id IN (1, 2, 3) ) t
where rn = 1

只添加ORDER BY子句，因为DISTINCT ON取有序组的第一条记录。

select distinct on (foreign_id) foreign_id, id, date
from table
where foreign_id IN (1, 2, 3)
order by foreign_id, date desc <<--- add this

您可以通过cte和row_number()实现这一点，如下所示:

with cte as (
select foreign_id,id,date, row_number()over (partition by foreign_id order by date desc) rownum
from t
)
select foreign_id,id,date from cte where rownum=1