From: Bryce Nesbitt <bryce1@obviously.com>
To: pgsql-sql@postgresql.org
Subject: Getting pk of the most recent row, in a group by
Date: Mon, 13 Aug 2007 13:05:40 -0700
Message-ID: <46C0B994.6090906@obviously.com> (raw)
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: bryce1@obviously.com
Subject: Re: Getting pk of the most recent row, in a group by
In-Reply-To: <46C0B994.6090906@obviously.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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