Received: from sss2.sss.pgh.pa.us (sss.pgh.pa.us [209.114.166.2]) by hub.org (8.9.3/8.9.3) with ESMTP id KAA50343 for ; Fri, 7 Apr 2000 10:25:22 -0400 (EDT) (envelope-from tgl@sss.pgh.pa.us) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss2.sss.pgh.pa.us (8.9.3/8.9.3) with ESMTP id KAA03811; Fri, 7 Apr 2000 10:25:14 -0400 (EDT) To: "Moray McConnachie" cc: "PGSQL SQL" Subject: Re: Maxima per row In-reply-to: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk> References: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk> Comments: In-reply-to "Moray McConnachie" message dated "Fri, 07 Apr 2000 10:36:52 +0100" Date: Fri, 07 Apr 2000 10:25:14 -0400 Message-ID: <3808.955117514@sss.pgh.pa.us> From: Tom Lane X-Archive-Number: 200004/72 "Moray McConnachie" 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