pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedGetting pk of the most recent row, in a group by
4+ messages / 4 participants
[nested] [flat]
* Getting pk of the most recent row, in a group by
@ 2007-08-13 20:05 Bryce Nesbitt <bryce1@obviously.com>
0 siblings, 3 replies; 4+ messages in thread
From: Bryce Nesbitt @ 2007-08-13 20:05 UTC (permalink / raw)
To: pgsql-sql
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?
--
----
Visit http://www.obviously.com/
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting pk of the most recent row, in a group by
@ 2007-08-13 20:16 Rodrigo De León <rdeleonp@gmail.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 4+ messages in thread
From: Rodrigo De León @ 2007-08-13 20:16 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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;
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting pk of the most recent row, in a group by
@ 2007-08-13 20:19 Michael Glaesemann <grzm@seespotcode.net>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 4+ messages in thread
From: Michael Glaesemann @ 2007-08-13 20:19 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Getting pk of the most recent row, in a group by
@ 2007-08-13 23:19 Terry Fielder <terry@ashtonwoodshomes.com>
parent: Bryce Nesbitt <bryce1@obviously.com>
2 siblings, 0 replies; 4+ messages in thread
From: Terry Fielder @ 2007-08-13 23:19 UTC (permalink / raw)
To: Bryce Nesbitt <bryce1@obviously.com>; +Cc: pgsql-sql
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?
>
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2007-08-13 23:19 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-13 20:05 Getting pk of the most recent row, in a group by Bryce Nesbitt <bryce1@obviously.com>
2007-08-13 20:16 ` Rodrigo De León <rdeleonp@gmail.com>
2007-08-13 20:19 ` Michael Glaesemann <grzm@seespotcode.net>
2007-08-13 23:19 ` Terry Fielder <terry@ashtonwoodshomes.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox