From: oheinz@stud.fbi.fh-darmstadt.de
To: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Wed, 30 Jul 2003 10:52:47 +0200
Message-ID: <1059555167.3f27875f7e3da@stud.fbi.fh-darmstadt.de> (raw)
In-Reply-To: <000401c355e4$11b0cdd0$0f02a8c0@spineofgod>
References: <1058964708.3f1e84e407a8d@stud.fbi.fh-darmstadt.de>
<200307280947.21658.josh@agliodbs.com>
<1059478450.3f265bb210e14@stud.fbi.fh-darmstadt.de>
<22142.1059490856@sss.pgh.pa.us>
<000401c355e4$11b0cdd0$0f02a8c0@spineofgod>
I did try the following:
SELECT DISTINCT ON (two.two_id) two.two_value FROM one,two WHERE
(one.two_id=two.two_id
AND one.updatenr > two.updatenr) ORDER BY two.updatenr ASC;
I thought this would
a) order the result list by updatenr
b) return the first record only for records that are equal on the two.two_id
field
which would return exactly what I need - return the record with the
But what I get instead is "ERROR: SELECT DISTINCT ON expressions must match
initial ORDER BY expressions" *grrrmpfh*
But after a while playing around I noticed that the "initial" in that sentence
above is important - adding two.two_id as first argument to the ORDER BY does
not change the result but fulfill the requirements - the expressions don't have
to be excactly the same - just the initial argument.
My view which returns the (hopefully) correct results:
SELECT DISTINCT ON (two.two_id) two.two_value FROM one, two WHERE ((one.two_id
= two.two_id) AND (one.updatenr > two.updatenr)) ORDER BY two.two_id,
two.updatenr;
Next step is to include corresponding information from table three - now I need
subselects right? no way around with this DISTINCT and ORDER by stuff in it ...
Many thanks for your help so far,
Oliver
Quoting Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>:
> I'll try this tomorrow - combining DISTINCT ON (two.two_id) and sorting by
> two.updatenr could (should) have the desired effect - I never thought about
> using ORDER and DISTINCT that way.
>
> I'll report my success or failure...
>
> Thanks so far!
>
> Bye,
> Oliver
-------------------------------------------------
This mail sent through IMP: http://horde.org/imp/
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: oheinz@stud.fbi.fh-darmstadt.de
Subject: Re: Problem using Subselect results
In-Reply-To: <1059555167.3f27875f7e3da@stud.fbi.fh-darmstadt.de>
* 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