Обсуждение: Getting pk of the most recent row, in a group by

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=150%  | 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?

-- 
----
Visit http://www.obviously.com/

On 8/13/07, Bryce Nesbitt <bryce1@obviously.com> wrote:
> In the second example, is it possible to get the primary key of the row
> with the minimum expires time?

SELECT   TYPE, MIN(expires), COUNT(*)      , (SELECT MIN(coupon_id)           FROM coupon          WHERE expires =
MIN(c.expires))AS coupon_id   FROM coupon c
 
GROUP BY TYPE;

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

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?
>
>