In-Reply-To: <46C0B994.6090906@obviously.com>
References: <46C0B994.6090906@obviously.com>
Mime-Version: 1.0 (Apple Message framework v752.2)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Message-Id: <B3C577C1-11B2-47CA-9066-B41A65C5F09A@seespotcode.net>
Cc: pgsql-sql@postgresql.org
Content-Transfer-Encoding: 7bit
From: Michael Glaesemann <grzm@seespotcode.net>
Subject: Re: Getting pk of the most recent row, in a group by
Date: Mon, 13 Aug 2007 15:19:05 -0500
To: Bryce Nesbitt <bryce1@obviously.com>


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