Behavior of subselects in target lists and order by

If we have a query of the form:

Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75

In the above query there is a subselect in the target list and the ORDERBY has an ordinal number which indicates order by column 1. Does this mean that the above query will return all results from T1 that match p3 =75 and all results from T2 that match p2 = T1.p1 for every match on T1 and order them all by the first column of T1 and T2 ?

basically i am trying to determine if the order by clause has effect only on the tuples of the outer select or both the outer and inner select. Or the results returned by the inner select are treated as if they are part of a single tuple which includes the tuple from table T1 ?

Is this an implementation specific behaviour or it conforms to the SQL standard ...?

thanks
-Amit

On 02/24/12 12:45 PM, amit sehas wrote:
> If we have a query of the form:
>
> Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75

ORDER BY has to be AFTER the WHERE clause.

is that query equivalent to...

Select t1.*, t2.* FROM T1 LEFT JOIN T2 on T1.p1=t2.p2 WHERE t1.p3 = 75 ORDER 1

?

>
> In the above query there is a subselect in the target list and the ORDERBY has an ordinal number which indicates order by column 1. Does this mean that the above query will return all results from T1 that match p3 =75 and all results from T2 that match p2 = T1.p1 for every match on T1 and order them all by the first column of T1 and T2 ?

I'm not sure your query as written would even work. if that inner
select was to return multiple rows, ugh.

> basically i am trying to determine if the order by clause has effect only on the tuples of the outer select or both the outer and inner select. Or the results returned by the inner select are treated as if they are part of a single tuple which includes the tuple from table T1 ?

you can't treat a recordset as a single tuple regardless. the ORDER BY
is always done on the data selected by the WHERE

> Is this an implementation specific behaviour or it conforms to the SQL standard ...?
>

I suspect its your proposed query that doesn't conform.

--
john r pierce N 37, W 122
santa cruz ca mid-left coast

On 2012-02-24, amit sehas <cun23(at)yahoo(dot)com> wrote:
> If we have a query of the form:
>
> Select *, (Select * FROM T2 WHERE p2 = T1.p1) FROM T1 ORDER BY 1 WHERE p3 = 75
>
> In the above query there is a subselect in the target list and the
> ORDERBY has an ordinal number which indicates order by column 1. Does
> this mean that the above query will return all results from T1 that
> match p3 =75 and all results from T2 that match p2 = T1.p1 for every
> match on T1 and order them all by the first column of T1 and T2 ?
>
> basically i am trying to determine if the order by clause has effect
> only on the tuples of the outer select or both the outer and inner
> select. Or the results returned by the inner select are treated as if
> they are part of a single tuple which includes the tuple from table T1 ?
>
> Is this an implementation specific behaviour or it conforms to the SQL standard ...?

with the exception of queries using "distinct on(...)" order by will
only effect the order in which the results are presented. Where
distinct on is used it will also effect which distinct subset set of
rows are presented.

Distinct on is outside the standard, otherwise postgres follows the
standard,

--
⚂⚃ 100% natural