Oliver,
> 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;
FWIW, SELECT DISTINCT ON () is slower than SELECT ORDER BY ... LIMIT 1 on
all stable versions of Postgres. Not that the LIMIT
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 th
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
[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.updaten
> Although in the simplistic examples above there's not much reason to use a
> subselect at all, of course.
O.K. my fault - and the subselects, now that i know not to use them on the
same level, seem not to be my real problem.
Another (hopefully better) example to show you what I was trying to
Oliver,
> CREATE VIEW my_view AS SELECT b,c
> (SELECT a, b FROM table2 WHERE b=1) my_ab,
> (SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c;
This isn't possible in PostgreSQL, and I'm not sure it's possible anywhere.
HOWEVER, if you put your subselects in the FROM clause instead, like
On Wed, Jul 23, 2003 at 14:51:48 +0200,
[EMAIL PROTECTED] wrote:
> I want to use the result of a subselect as condition of another one.
The two selects you use ar both from items at the same level and hence can't
reference one another. In your example below you could just use a join.
> CREATE V
I want to use the result of a subselect as condition of another one.
CREATE VIEW my_view AS SELECT b,c
(SELECT a, b FROM table2 WHERE b=1) my_ab,
(SELECT c FROM table3, my_ab WHERE table3.a=my_ab.a) my_c;
does return "relation my_ab unknown". it is not just a problem of execution
order - if i t
[EMAIL PROTECTED] wrote:
Quoting Dmitry Tkach <[EMAIL PROTECTED]>:
What about:
CREATE VIEW my_view AS SELECT b,c from
(SELECT a, b FROM table1 WHERE b=1) as my_ab,
(SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
This looks like what you are trying to do, and doesn't use that
'subs
[EMAIL PROTECTED] writes:
> Quoting Dmitry Tkach <[EMAIL PROTECTED]>:
>> CREATE VIEW my_view AS SELECT b,c from
>> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
>> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
> I assume that with this statement postgresql will compute both subselects,
Quoting Dmitry Tkach <[EMAIL PROTECTED]>:
> What about:
>
> CREATE VIEW my_view AS SELECT b,c from
> (SELECT a, b FROM table1 WHERE b=1) as my_ab,
> (SELECT a, c FROM table2) as my_ac WHERE my_ac.a=my_ab.a
>
> This looks like what you are trying to do, and doesn't use that
> 'subsubselect' you w
[EMAIL PROTECTED] wrote:
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT
table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a =
my_ab.a)) my_c;
You were doing what I wanted to avoid - you are using a "SUBSUBSELECT".
What about:
CREATE VIEW m
SELECT my_c.b, my_c.c FROM (SELECT table2.b, table2.c FROM table2, (SELECT
table1.a, table1.b FROM table1 WHERE (table1.b = 1)) my_ab WHERE (table2.a =
my_ab.a)) my_c;
You were doing what I wanted to avoid - you are using a "SUBSUBSELECT".
But (now) I believe it's not possible to refer to a s
> I want to use the result of a subselect as condition in another one.
>
> table1: a,b
> table2: a,c
>
> CREATE VIEW my_view AS SELECT b,c
> (SELECT a, b FROM table1 WHERE b=3D1) my_ab,
> (SELECT c FROM table2, my_ab WHERE table3.a=3Dmy_ab.a) my_c;
>
> this is just an example - i know i could cros
Hi all,
I want to use the result of a subselect as condition in another one.
table1: a,b
table2: a,c
CREATE VIEW my_view AS SELECT b,c
(SELECT a, b FROM table1 WHERE b=1) my_ab,
(SELECT c FROM table2, my_ab WHERE table3.a=my_ab.a) my_c;
this is just an example - i know i could cross join this o
15 matches
Mail list logo
