pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedSubquery error. Help please!!
4+ messages / 4 participants
[nested] [flat]
* Subquery error. Help please!!
@ 2001-06-27 21:09 kakerjak <kakarotto@canada.com>
0 siblings, 3 replies; 4+ messages in thread
From: kakerjak @ 2001-06-27 21:09 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subquery error. Help please!!
@ 2001-06-28 14:46 Wei Weng <wweng@kencast.com>
parent: kakerjak <kakarotto@canada.com>
2 siblings, 0 replies; 4+ messages in thread
From: Wei Weng @ 2001-06-28 14:46 UTC (permalink / raw)
To: kakerjak <kakarotto@canada.com>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subquery error. Help please!!
@ 2001-06-28 14:54 Tom Lane <tgl@sss.pgh.pa.us>
parent: kakerjak <kakarotto@canada.com>
2 siblings, 0 replies; 4+ messages in thread
From: Tom Lane @ 2001-06-28 14:54 UTC (permalink / raw)
To: kakerjak <kakarotto@canada.com>; +Cc: pgsql-sql
"kakerjak" <kakarotto@canada.com> 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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Subquery error. Help please!!
@ 2001-06-28 17:11 Ross J. Reedstrom <reedstrm@rice.edu>
parent: kakerjak <kakarotto@canada.com>
2 siblings, 0 replies; 4+ messages in thread
From: Ross J. Reedstrom @ 2001-06-28 17:11 UTC (permalink / raw)
To: kakerjak <kakarotto@canada.com>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2001-06-28 17:11 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-06-27 21:09 Subquery error. Help please!! kakerjak <kakarotto@canada.com>
2001-06-28 14:46 ` Wei Weng <wweng@kencast.com>
2001-06-28 14:54 ` Tom Lane <tgl@sss.pgh.pa.us>
2001-06-28 17:11 ` Ross J. Reedstrom <reedstrm@rice.edu>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox