pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Maxima 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