在GROUP BY查询之前获取最新条目
问题描述:
使用postgres,我想检索每个不同的软件包,但检索每个组的最新软件包。
Using postgres, I would like to retrieve each different packages, but retrieve the most recent package for each group.
例如,我有一个名为product的表:
For example, I have this table named products:
package_name price date
- | 52500.0 | 20080910
- | 52900.0 | 20090422
- | 52900.0 | 20090706
ELITE PACKAGE | 62200.0 | 20080910
ELITE PACKAGE | 62500.0 | 20090706
ELITE PACKAGE | 62500.0 | 20090422
TECH PACKAGE | 57200.0 | 20080910
TECH PACKAGE | 58200.0 | 20090706
TECH PACKAGE | 58200.0 | 20090422
我想通过SQL查询获得以下结果:
And I would like to get the following result with a SQL query:
- | 52900.0 | 20090706
ELITE PACKAGE | 62500.0 | 20090706
TECH PACKAGE | 58200.0 | 20090706
我已经尝试了几项(我认为)可以在mySQL中工作的方法,但是我
I have tried a couple of things that (I think) would have worked in mySQL, but I can't get it right for postgres.
有什么可能做的,我该怎么做?
Is it something possible to do, and how would I do it?
谢谢
斯蒂芬妮
答
SELECT t.package_name, p.price, t.MaxDate
FROM (SELECT package_name, MAX(date) AS MaxDate
FROM products
GROUP BY package_name) t
INNER JOIN products p
ON t.package_name = p.package_name
and t.MaxDate = p.date