在GROUP BY查询之前获取最新条目 [英] Get the newest entry before a GROUP BY query

问题描述

使用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

这篇关于在GROUP BY查询之前获取最新条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！