From: Tom Lane <tgl@sss.pgh.pa.us>
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
Cc: PGSQL SQL <pgsql-sql@postgresql.org>
Subject: Re: Maxima per row
Date: Fri, 07 Apr 2000 10:25:14 -0400
Message-ID: <3808.955117514@sss.pgh.pa.us> (raw)
In-Reply-To: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk>
References: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk>
"Moray McConnachie" <moray.mcconnachie@computing-services.oxford.ac.uk> writes:
> I have a table, one of whose fields indicates the date from which
> information in the row becomes valid, and another indicates the type to
> which the row refers. How do I design a query such that I get one row for
> each type, that row being the most recent info about that type?
Of course, if you *only* want to get the typeid and date, it's easy:
SELECT typeid, max(startdate) FROM table GROUP BY typeid;
The tricky part is getting back the rest of the row that contains the
maximum startdate value. This approach can't do that.
You can do it with a subselect:
SELECT typeid, startdate, ... FROM table outer WHERE startdate =
(SELECT max(startdate) FROM table inner
WHERE inner.typeid = outer.typeid);
or perhaps faster
SELECT typeid, startdate, ... FROM table outer WHERE NOT EXISTS
(SELECT 1 FROM table inner
WHERE inner.typeid = outer.typeid AND
inner.startdate > outer.startdate);
but both of these are likely to be pretty slow, and they're not that
easy to understand either. If you don't mind using non-SQL-standard
features, another way is with DISTINCT ON:
SELECT DISTINCT ON (typeid) typeid, startdate, ... FROM table
ORDER BY typeid, startdate DESC;
(In 6.5, omit parentheses around DISTINCT ON argument.) This orders
the data in the specified way and then drops all but the first row
of each group with the same typeid. Since each such group is ordered
by startdate, you have your result.
regards, tom lane
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 from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, moray.mcconnachie@computing-services.oxford.ac.uk
Subject: Re: Maxima per row
In-Reply-To: <3808.955117514@sss.pgh.pa.us>
References: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk> <3808.955117514@sss.pgh.pa.us>
* 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