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