Re: BUG #1335: Wrong sort result in union queries

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.