pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: kakerjak <kakarotto@canada.com>
To: pgsql-sql@postgresql.org
Subject: Subquery error. Help please!!
Date: Wed, 27 Jun 2001 17:09:14 -0400
Message-ID: <9hdi36$scd$1@news.tht.net> (raw)

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





view thread (4+ messages)  latest in thread

Message-ID: <9hdi36$scd$1@news.tht.net>
Permalink:  ../9hdi36$scd$1@news.tht.net/
Also on:    postgresql.org/message-id/9hdi36$scd$1@news.tht.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: kakarotto@canada.com
  Subject: Re: Subquery error. Help please!!
  In-Reply-To: <9hdi36$scd$1@news.tht.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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