Re: [SQL] 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. ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [SQL] Subquery error. Help please!!
kakerjak [EMAIL PROTECTED] writes: 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 (' Are you using 7.1? regards, tom lane ---(end of broadcast)--- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [SQL] 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]