Thread: ordering of selected rows from an ordered subselect

Hi,

we know that rows in a table are not stored in any particular order
and explicit order by clause is required to get data in any particular
order.

but does it apply to select queries from ordered subselects also ?
eg

select   id , name , expensive_func(name)  from       ( select id , name from tab  order by c1 desc limit 15)  as foo
;

is it guaranteed that the final result is order by c1 ?

Regds
mallah.

On Sat, Feb 17, 2007 at 03:02:06PM +0530, Rajesh Kumar Mallah wrote:
> 
> select   id , name , expensive_func(name)  from
>        ( select id , name from tab  order by c1 desc limit 15)  as foo ;
> 
> is it guaranteed that the final result is order by c1 ?

No, because expensive_func(name) might do something that alters it,
no?  All things considered, it's a pretty good _bet_ it will be
ordered as you wish, though.

A

-- 
Andrew Sullivan  | ajs@crankycanuck.ca
The whole tendency of modern prose is away from concreteness.    --George Orwell

"Rajesh Kumar Mallah" <mallah.rajesh@gmail.com> writes:
> select   id , name , expensive_func(name)  from
>         ( select id , name from tab  order by c1 desc limit 15)  as foo ;
> is it guaranteed that the final result is order by c1 ?

The sub-select's output will be emitted in the specified order.  What
happens after that depends on the outer query, but if you don't have any
joining or grouping then it's a reasonably safe bet that the final
output will be in the same order.
        regards, tom lane