Thread: 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
Kaloyan Iliev Iliev <news1@faith.digsys.bg> 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
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 <news1@faith.digsys.bg> 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 > > > >
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
Kaloyan Iliev Iliev <news1@faith.digsys.bg> 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) ->
SubqueryScan *SELECT* 1 (cost=0.00..380.00 rows=10000 width=148) -> Seq Scan on tenk1 a
(cost=0.00..380.00rows=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