Thread: UNION and ORDER BY

With:

(select *
from table1
where col1=1
order by name)
union
(select *
from table1
where col1=2
order by random())

UNION apparently reorders all the rows of the result
in their natural order (sequence in which they where
inserted). Is there a way to have the first part of
the results ordered by name, and the second part by random()?

__________________________________________________
Do you Yahoo!?
U2 on LAUNCH - Exclusive greatest hits videos
http://launch.yahoo.com/u2

I *think* if you use unionall, it should do what you want here...

I hope, it helps.

Dima

Cool Screen wrote:
> With:
>
> (select *
> from table1
> where col1=1
> order by name)
> union
> (select *
> from table1
> where col1=2
> order by random())
>
> UNION apparently reorders all the rows of the result
> in their natural order (sequence in which they where
> inserted). Is there a way to have the first part of
> the results ordered by name, and the second part by random()?
>
> __________________________________________________
> Do you Yahoo!?
> U2 on LAUNCH - Exclusive greatest hits videos
> http://launch.yahoo.com/u2
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html
>

Cool Screen <cool_screen_name90001@yahoo.com> writes:
> UNION apparently reorders all the rows of the result
> in their natural order

No.  It sorts them so it can eliminate duplicate rows, as per spec.
Try UNION ALL if you just want the two subselect results concatenated.

            regards, tom lane