Problems with UNION ALL and ORDER BY

Dear friends...,
I have the following problem:


select
......
from
.....
where
.....
UNION ALL

select
....
from
.....
where
.....
ORDER BY field1

But the the order by doesn't work properly. It returns the rows of the
first query ordered and then appends the rows of the second query
ordered. But this is not what i expect. I expect the result of both
queries to be orderd. So I try.

SELECT TEMP.*
FROM
(
select
......
from
.....
where
.....
UNION ALL

select
....
from
.....
where
.....
) TEMP
ORDER BY TEMP.field1

But this also doesn't work. Any ideas.p

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

Re: Problems with UNION ALL and ORDER BY

Kaloyan Iliev Iliev writes:
> I have the following problem:

> select
> .....
> from
> ....
> where
> ....
> UNION ALL

> select
> ...
> from
> ....
> where
> ....
> ORDER BY field1

> But the the order by doesn't work properly. It returns the rows of the
> first query ordered and then appends the rows of the second query
> ordered.

Pray tell, what Postgres release are you using?

AFAICT this will result in an overall sort in all PG releases since 7.0.
I don't have anything older to test...

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Problems with UNION ALL and ORDER BY

Dear Tom,

As I say in my previous letter I am using 7.2.3. If you wish I can show
you the query and the result to see for yourself, that there is
something wrong. It just don't order the overall result but the separate
results of the both subqueries.

Tom Lane wrote:

>Kaloyan Iliev Iliev writes:
>
>
>> I have the following problem:
>>
>>
>
>
>
>>select
>>.....
>>from
>>....
>>where
>>....
>> UNION ALL
>>
>>
>
>
>
>>select
>>...
>>from
>>....
>>where
>>....
>>ORDER BY field1
>>
>>
>
>
>
>>But the the order by doesn't work properly. It returns the rows of the
>>first query ordered and then appends the rows of the second query
>>ordered.
>>
>>
>
>Pray tell, what Postgres release are you using?
>
>AFAICT this will result in an overall sort in all PG releases since 7.0.
>I don't have anything older to test...
>
> regards, tom lane
>
>
>
>

---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Re: Problems with UNION ALL and ORDER BY

Kaloyan Iliev Iliev wrote:
>
> Dear friends...,
> I have the following problem:
>
>
> select ..... from .... where ....
> UNION ALL
>
> select ... from .... where ....
> ORDER BY field1
>
> But the the order by doesn't work properly. It returns the rows of the
> first query ordered and then appends the rows of the second query
> ordered. But this is not what i expect. I expect the result of both
> queries to be orderd. So I try.
>
> SELECT TEMP.*
> FROM
> (
> select ..... from .... where ....
> UNION ALL
>
> select ... from .... where ....
> ) TEMP
> ORDER BY TEMP.field1
>
> But this also doesn't work. Any ideas.p

This have to work. Could you please provide a real example ( creation table,
insertion data and query execution ).


Regards
Gaetano Mendola

Re: Problems with UNION ALL and ORDER BY

Kaloyan Iliev Iliev writes:
> As I say in my previous letter I am using 7.2.3. If you wish I can show
> you the query and the result to see for yourself, that there is
> something wrong. It just don't order the overall result but the separate
> results of the both subqueries.

Quite honestly, I don't believe it. In 7.2 I get

regression=# explain select * from tenk1 a union all select * from tenk1 b order by unique1;
NOTICE: QUERY PLAN:

Sort (cost=3128.28..3128.28 rows=20000 width=148)
-> Append (cost=0.00..760.00 rows=20000 width=148)
-> Subquery Scan *SELECT* 1 (cost=0.00..380.00 rows=10000 width=148)
-> Seq Scan on tenk1 a (cost=0.00..380.00 rows=10000 width=148)
-> Subquery Scan *SELECT* 2 (cost=0.00..380.00 rows=10000 width=148)
-> Seq Scan on tenk1 b (cost=0.00..380.00 rows=10000 width=148)

EXPLAIN

and as you can see there's only one sort step being applied to the union
result.

Now if EXPLAIN shows you a different sorting structure for your query,
then I'd be interested to see the exact query and the EXPLAIN output.
But what I think is that you are misinterpreting the sorting result you
get. If you are using a non-C locale you may be seeing some pretty
weird sorting rules :-(

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly