From: Oliver Heinz <oheinz@stud.fbi.fh-darmstadt.de>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-sql@postgresql.org
Subject: Re: Problem using Subselect results
Date: Tue, 29 Jul 2003 17:14:12 +0200
Message-ID: <000401c355e4$11b0cdd0$0f02a8c0@spineofgod> (raw)
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>
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
----- Original Message -----
From: "Tom Lane" <tgl@sss.pgh.pa.us>
To: <oheinz@stud.fbi.fh-darmstadt.de>
Cc: <pgsql-sql@postgresql.org>
Sent: Tuesday, July 29, 2003 5:00 PM
Subject: Re: [SQL] Problem using Subselect results
> oheinz@stud.fbi.fh-darmstadt.de writes:
> > But as this data is time sensitive, we introduce some kind of time
stamp - a
> > serial which is global to all tables. Now, for each record in table
'one' i
> > want to see only the corresponding records in tables two, three, etc...
that
> > were created before 'one.updatenr'
>
> > SELECT * FROM one, two WHERE (one.two_id=two.two_id AND one.updatenr >
> > two.updatenr);
>
> > This might match multiple records in tables two (two_id is not a pk, we
have
> > historic records in this table). Now I want only the most current
version
> > before one.updatenr. - And that's where I run into trouble.
>
> You might be able to make this work by using SELECT DISTINCT ON. See
> the "weather reports" example in the SELECT reference page.
>
> regards, tom lane
>
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, tgl@sss.pgh.pa.us
Subject: Re: Problem using Subselect results
In-Reply-To: <000401c355e4$11b0cdd0$0f02a8c0@spineofgod>
* 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