[EMAIL PROTECTED] 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
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
