Postgresql extract last row for each id

Suppose I've next data

  id    date          another_info
1     2014-02-01         kjkj
1     2014-03-11         ajskj
1     2014-05-13         kgfd
2     2014-02-01         SADA
3     2014-02-01         sfdg
3     2014-06-12         fdsA

I want for each id extract last information:

  id    date          another_info
1     2014-05-13         kgfd
2     2014-02-01         SADA
3     2014-06-12         fdsA

How could I manage that?

71006 次浏览
小开
最佳答案

最有效的方法是使用 Postgres 的 distinct on操作符

select distinct on (id) id, date, another_info
from the_table
order by id, date desc;

If you want a solution that works across databases (but is less efficient) you can use a window function:

select id, date, another_info
from (
select id, date, another_info,
row_number() over (partition by id order by date desc) as rn
from the_table
) t
where rn = 1
order by id;

在大多数情况下，使用窗口函数的解决方案比使用子查询更快。

小开

按 id 分组，并使用任何聚合函数来满足上次记录的条件

select  id, max(date), another_info
from the_table
group by id, another_info
小开 
select *
from bar
where (id,date) in (select id,max(date) from bar group by id)

在 PostgreSQL 和 MySQL 中进行测试

小开

我发现这是最快的解决方案:

 SELECT t1.*
FROM yourTable t1
LEFT JOIN yourTable t2 ON t2.tag_id = t1.tag_id AND t2.value_time > t1.value_time
WHERE t2.tag_id IS NULL

提交答案