Received: from localhost (maia-1.hub.org [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id E303A9F95AB for ; Mon, 13 Aug 2007 20:22:34 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 64360-02 for ; Mon, 13 Aug 2007 20:22:31 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from mail.greatgulfhomes.com (mail.greatgulfhomes.com [216.191.52.66]) by postgresql.org (Postfix) with ESMTP id B17BF9F96B4 for ; Mon, 13 Aug 2007 20:22:31 -0300 (ADT) Received: from [10.243.102.72] ([216.191.52.74]) (authenticated bits=0) by mail.greatgulfhomes.com (8.13.8/8.13.8) with ESMTP id l7DNMQcq030301; Mon, 13 Aug 2007 19:22:26 -0400 Message-ID: <46C0E6EE.6080805@ashtonwoodshomes.com> Date: Mon, 13 Aug 2007 19:19:10 -0400 From: Terry Fielder User-Agent: Thunderbird 1.5.0.12 (Windows/20070509) MIME-Version: 1.0 To: Bryce Nesbitt CC: pgsql-sql@postgresql.org Subject: Re: Getting pk of the most recent row, in a group by References: <46C0B994.6090906@obviously.com> In-Reply-To: <46C0B994.6090906@obviously.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Greylist: Sender succeeded SMTP AUTH authentication, not delayed by milter-greylist-3.0 (mail.greatgulfhomes.com [216.191.52.66]); Mon, 13 Aug 2007 19:22:26 -0400 (EDT) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.255 tagged_above=0 required=5 tests=AWL=0.254, BAYES_50=0.001 X-Spam-Level: X-Archive-Number: 200708/119 X-Sequence-Number: 29057 Do you have a table of coupon types? Terry Terry Fielder terry@greatgulfhomes.com Associate Director Software Development and Deployment Great Gulf Homes / Ashton Woods Homes Fax: (416) 441-9085 Bryce Nesbitt wrote: > I've got a table of "coupons" which have an expiration date. For each > type of coupon, I'd like to get the primary key of the coupon which will > expire first. > > # create table coupon > ( > coupon_id serial primary key, > type varchar(255), > expires date > ); > insert into coupon values(DEFAULT,'free','2007-01-01'); > insert into coupon values(DEFAULT,'free','2007-01-01'); > insert into coupon values(DEFAULT,'free','2007-06-01'); > insert into coupon values(DEFAULT,'free','2007-06-01'); > insert into coupon values(DEFAULT,'50%','2008-06-01'); > insert into coupon values(DEFAULT,'50%','2008-06-02'); > insert into coupon values(DEFAULT,'50%','2008-06-03'); > > The desired query would look like: > > # select coupon_id,type,expires from coupon where type='free' order by > expires limit 1; > coupon_id | type | expires > -----------+------+------------ > 1 | free | 2007-01-01 > > > But be grouped by type: > > # 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? > >