Re: Subquery error. Help please!!

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.