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. ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
