Re: [SQL] Getting pk of the most recent row, in a group by

[Michael Glaesemann]

On Aug 13, 2007, at 15:05 , Bryce Nesbitt wrote:
# select type,min(expires),count(*) from coupon group by type;
 type |    min     | count
------+------------+-------
 free | 2007-01-01 |     4    ; pk=1
 50%  | 2008-06-01 |     3    ; pk=5

In the second example, is it possible to get the primary key of the  
row
with the minimum expires time?

I believe DISTINCT ON will do what you want, if you don't mind using  
non-SQL-spec functionality:

SELECT DISTINCT ON (type)
        type, expires, coupon_id
FROM coupon
ORDER BY type, expires;

I believe you'd need to add the COUNT using a join:

SELECT type, expires, coupon_id, type_count
FROM (
    SELECT DISTINCT ON (type)
        type, expires, coupon_id
        FROM coupon
        ORDER BY type, expires
    ) earliest_to_expire
JOIN (
    SELECT type, count(coupon_id) as type_count
    FROM coupons
    GROUP BY type
    ) type_counts USING (type);

Michael Glaesemann
grzm seespotcode net



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings