Received: from postgresql.org.org (webmail.postgresql.org [216.126.85.28]) by postgresql.org (8.11.3/8.11.1) with SMTP id f5SCYOa92096 for ; Thu, 28 Jun 2001 08:34:24 -0400 (EDT) (envelope-from pgsql-sql-owner@postgresql.org) Received: from news.tht.net (news.hub.org [216.126.91.242]) by postgresql.org (8.11.3/8.11.1) with ESMTP id f5RLW2a02971 for ; Wed, 27 Jun 2001 17:32:02 -0400 (EDT) (envelope-from news@news.tht.net) Received: (from news@localhost) by news.tht.net (8.11.4/8.11.4) id f5RLA0J29072 for pgsql-sql@postgresql.org; Wed, 27 Jun 2001 17:10:00 -0400 (EDT) (envelope-from news) From: "kakerjak" X-Newsgroups: comp.databases.postgresql.sql Subject: Subquery error. Help please!! Date: Wed, 27 Jun 2001 17:09:14 -0400 Organization: none Lines: 44 Message-ID: <9hdi36$scd$1@news.tht.net> X-Complaints-To: scrappy@hub.org X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 5.50.4522.1200 X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4522.1200 To: pgsql-sql@postgresql.org X-Archive-Number: 200106/442 X-Sequence-Number: 3739 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