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.