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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Cc: <[EMAIL PROTECTED]>
Sent: Tuesday, July 29, 2003 5:00 PM
Subject: Re: [SQL] Problem using Subselect results
> [EMAIL PROTECTED] 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
>
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
