Received: from ece.rice.edu (ece.rice.edu [128.42.4.34]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SHCAa38335 for ; Thu, 28 Jun 2001 13:12:11 -0400 (EDT) (envelope-from reedstrm@rice.edu) Received: from wallace.ece.rice.edu (wallace.ece.rice.edu [128.42.12.154]) by ece.rice.edu (Postfix) with ESMTP id 7100A68A5C; Thu, 28 Jun 2001 12:12:07 -0500 (CDT) Received: from reedstrm by wallace.ece.rice.edu with local (Exim 3.22 #1 (Debian)) id 15FfKZ-0003l0-00; Thu, 28 Jun 2001 12:11:51 -0500 Date: Thu, 28 Jun 2001 12:11:51 -0500 From: "Ross J. Reedstrom" To: kakerjak Cc: pgsql-sql@postgresql.org Subject: Re: Subquery error. Help please!! Message-ID: <20010628121151.C14260@rice.edu> Mail-Followup-To: "Ross J. Reedstrom" , kakerjak , pgsql-sql@postgresql.org References: <9hdi36$scd$1@news.tht.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <9hdi36$scd$1@news.tht.net> User-Agent: Mutt/1.3.18i X-Archive-Number: 200106/454 X-Sequence-Number: 3757 I did something similar, but pu the subselect into a view, in the WHERE clause. SELECT * FROM modules m WHERE (m.module_ident = (SELECT max(modules.module_ident) AS max FROM modules WHERE (m.moduleid = modules.moduleid) GROUP BY modules.moduleid)); The equivalent for you would be something like: SELECT * FROM laboratory l where l.subid = (select max(laboratory.subid) from laboratory WHERE (l.subid = laboratory.subid) GROUP BY laboratory.id); And make sure you have indices on both id and subid. Ross On Wed, Jun 27, 2001 at 05:09:14PM -0400, kakerjak wrote: > Hey all.. > > Here's the table definition. > CREATE TABLE "laboratory" ( > "id" "int4" NOT NULL, > "subid" "int2" NOT NULL, > "name" varchar(30) NOT NULL, > CONSTRAINT "laboratory_pkey" PRIMARY KEY ("id", "subid")) > > The way this table works is that each lab has one ID. If any information is > changed(there are actually more fields then what i showed, but they don't > affect the problem) then the ID remains the same, but the SUBID gets > incremented by 1. Thus, other tables linking to it just need to know the ID > and then read the ID with the biggest SUBID to get the most recent record. > > Now, what I want to do is this. Create a list of all the laboratories using > only the most recent record for each (the biggest SUBID for every unique > ID). > > Here's my select statement. > > SELECT b.id, b.subid, b.name > FROM (SELECT c.id, MAX(c.subid) AS subid FROM laboratory AS c GROUP BY > id) AS a > INNER JOIN > laboratory AS b > USING id, subid > > The subquery works on it's own and returns the desired ID, SUBID > combinations. > But when put into the other query I get parser errors. > If the subquery is placed before the JOIN, like it is above, then the error > i get says 'parse error at or near "select"' > If i flip the subquery around with the laboratory table then i get 'parse > error at or near "("' > > According to the documention online, it seems as if this statement should > work. > ( http://www.postgresql.org/idocs/index.php?queries.html#QUERIES-FROM ) > > Any help would be appreciated. TIA > kakerjak > > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster