PostgreSQL提取每个ID的最后一行(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?
解决方案
The most efficient way is to use Postgres' distinct on operator
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;
The solution with a window function is in most cases faster than using a sub-query.
假设我有下一个数据
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
我想为每个id提取最新信息：
id date another_info
1 2014-05-13 kgfd
2 2014-02-01 SADA
3 2014-06-12 fdsA
我该如何处理？
解决方案
最有效的方法是在运算符上使用Postgres的 distinct
从（_id）id，日期，the_table
的id，日期desc中选择另一个信息
；
如果您想要一个跨数据库的解决方案（但效率较低），则可以使用窗口函数：
选择ID，日期，another_info
from（
选择ID，日期，another_info，
row_number（）结束（按日期desc按ID顺序划分），从the_table
中为rn
）t
其中rn = 1
，按id排序；
带有窗口功能的解决方案在大多数情况下比使用子查询要快。
