Received: from oxmail.ox.ac.uk (oxmail1.ox.ac.uk [129.67.1.1]) by hub.org (8.9.3/8.9.3) with ESMTP id FAA16137 for ; Fri, 7 Apr 2000 05:38:03 -0400 (EDT) (envelope-from moray.mcconnachie@computing-services.oxford.ac.uk) Received: from ermine.ox.ac.uk ([163.1.2.13]) by oxmail.ox.ac.uk with esmtp (Exim 2.10 #1) id 12dVDF-0000Y6-00 for pgsql-sql@postgresql.org; Fri, 7 Apr 2000 10:38:01 +0100 Received: from ermine.ox.ac.uk ([163.1.2.13] helo=moraypc ident=root) by ermine.ox.ac.uk with smtp (Exim 3.13 #1) id 12dVDF-0004ZX-00 for pgsql-sql@postgreSQL.org; Fri, 07 Apr 2000 10:38:01 +0100 Message-ID: <00a201bfa074$ce92f050$760e01a3@oucs.ox.ac.uk> From: "Moray McConnachie" To: "PGSQL SQL" Subject: Maxima per row Date: Fri, 7 Apr 2000 10:36:52 +0100 MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 5.00.2919.6700 X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2919.6700 X-Archive-Number: 200004/63 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