Geek asks and answers

Get a new record before querying GROUP BY

Using postgres, I would like to get all the different packages, but get the last package for each group.

For example, I have this table called 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

And I would like to get the following result with an SQL query:

-                   |   52900.0 |   20090706
ELITE PACKAGE       |   62500.0 |   20090706
TECH PACKAGE        |   58200.0 |   20090706

I tried a couple of things that (I think) would work in mySQL, but I can't figure it out right for postgres.

Can this be done, and how would I do it?

Thank,

Stephanie

+3
source share
2 answers
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
+3
source

Another option that avoids self-joining (I always hate myself)

SELECT package_name,price,date FROM
(
 SELECT package_name,price,date,
        ROW_NUMBER() OVER (PARTITION BY package_name ORDER BY date DESC) as rw
 FROM products
) a
WHERE rw=1;
+5
source

More articles:


All Articles