Re: Behavior of subselects in target lists and order by

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