Skip site navigation (1) Skip section navigation (2)

Re: order by using functions under unions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: paree(at)lexum(dot)umontreal(dot)ca, pgsql-bugs(at)postgresql(dot)org
Subject: Re: order by using functions under unions
Date: 2001-05-10 21:50:50
Message-ID: 25313.989531450@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
pgsql-bugs(at)postgresql(dot)org writes:
> select name from temp where name like 'e%'
> union
> select name from temp where name not like 'e%'
> order by substr(name,1,4) asc;

This isn't supported.  7.1 knows that it can't do it:

regression=# select name from temp where name like 'e%'
regression-# union
regression-# select name from temp where name not like 'e%'
regression-# order by substr(name,1,4) asc;
ERROR:  ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of the result columns

It'd be nice to make it happen for the case you illustrate (ORDER BY on
an expression using only result columns) but that's not done yet.

In the meantime you can work around it (again, in 7.1) by using an
explicit subselect:

regression=# select name from (
regression(# select name from temp where name like 'e%'
regression(# union
regression(# select name from temp where name not like 'e%'
regression(# ) ss
regression=# order by substr(name,1,4) asc;
  name
---------
 chantal
 daniel
 eric
 ernst
(4 rows)

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: AmitDate: 2001-05-11 05:47:22
Subject: Problem in Porting from Oracle to Postgres
Previous:From: pgsql-bugsDate: 2001-05-10 19:29:59
Subject: order by using functions under unions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group