Re: Maxima per row

"Moray McConnachie" <moray(dot)mcconnachie(at)computing-services(dot)oxford(dot)ac(dot)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