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

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