On Wed, 1 Dec 2004, PostgreSQL Bugs List wrote:
> Query:
> select 2 union select 1
>
> Result:
> 1
> 2
>
> Why? I think the result must be like this:
> 2
> 1
If you don't specify an order by (at the top level) the output has no
defined order by SQL, so both orders are valid.
> Why PostgreSQL sort union queries by first column by default?
> Certainly, I understand that I can write general "order by" in the end of
> query. However, in this case, I can't make queries with "manual" row ording.
Union isn't a tool which gives you that ability. Union All is closer, but
still doesn't guarantee an order.
The only one I can think of is to assign weights to the rows as you're
going and ordering by that at the top level.
> (select * from (select 1, 2 union select 4, 3) as a
> order by 2 desc)
> union
> select 1, 1
>
> Result must be like this:
> 4, 3
> 1, 2
> 1, 1
>
> but real result is:
> 1, 1
> 1, 2
> 4, 3
This one is also okay. The order by in one wing does not control the
output of the union. I believe supporting it is an extension in any case
(at least SQL92 seems to make it illegal) and is probably meant for
interaction with limit.