pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedMaxima per row
2+ messages / 2 participants
[nested] [flat]
* Maxima per row
@ 2000-04-07 09:36 Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
0 siblings, 1 reply; 2+ messages in thread
From: Moray McConnachie @ 2000-04-07 09:36 UTC (permalink / raw)
To: pgsql-sql
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? I.e. what I
want is
CREATE TABLE info (id serial primary key,typeid int4,validfrom
date,description text);
INSERT INTO info (typeid,validfrom,description) VALUES (1,'01/01/1999','a');
INSERT INTO info (typeid,validfrom,description) VALUES (1,'01/01/2000','b');
INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1998','c');
INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1999','d');
INSERT INTO info (typeid,validfrom,description) VALUES (2,'01/01/1997','e');
SELECT typeid,validfrom FROM prices GROUP BY typeid HAVING
validfrom=MAX(validfrom);
but of course the last is illegal. The result I'm looking for is
1,'01/01/2000','b',
2,'01/01/1999','d'
Thanks,
Moray
----------------------------------------------------------------
Moray.McConnachie@computing-services.oxford.ac.uk
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Maxima per row
@ 2000-04-07 14:25 Tom Lane <tgl@sss.pgh.pa.us>
parent: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2000-04-07 14:25 UTC (permalink / raw)
To: Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>; +Cc: pgsql-sql
"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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2000-04-07 14:25 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2000-04-07 09:36 Maxima per row Moray McConnachie <moray.mcconnachie@computing-services.oxford.ac.uk>
2000-04-07 14:25 ` Tom Lane <tgl@sss.pgh.pa.us>
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