Re: Subquery error. Help please!!

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