Thread: Maxima per row

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

"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.typeidAND       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 tableORDER 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