Received: from smtp.snet.net (smtp.snet.net [204.60.6.55]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5SDeba26112 for ; Thu, 28 Jun 2001 09:40:37 -0400 (EDT) (envelope-from wweng@kencast.com) Received: from pop.snet.net (pop.snet.net [204.60.6.9]) by smtp.snet.net (8.11.1/8.11.1/SNET-mx-1.4/D-1.10/O-1.7) with ESMTP id f5SDeWC29938; Thu, 28 Jun 2001 09:40:32 -0400 (EDT) Received: from [192.168.1.83] (167.95.252.64.snet.net [64.252.95.167]) by pop.snet.net (8.11.1/8.11.1/SNET-pop-1.5/D-1.8/O-1.6) with ESMTP id f5SDeVr28426; Thu, 28 Jun 2001 09:40:31 -0400 (EDT) Subject: Re: Subquery error. Help please!! From: Wei Weng To: kakerjak Cc: pgsql-sql@postgresql.org In-Reply-To: <9hdi36$scd$1@news.tht.net> References: <9hdi36$scd$1@news.tht.net> Content-Type: text/plain X-Mailer: Evolution/0.10 (Preview Release) Date: 28 Jun 2001 10:46:12 -0400 Message-Id: <993739583.577.0.camel@Monet> Mime-Version: 1.0 X-Archive-Number: 200106/445 X-Sequence-Number: 3746 What version of postgresql are you using? On 27 Jun 2001 17:09:14 -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 > -- Wei Weng Network Software Engineer KenCast Inc.