Using Postgresql 8.2.3
The following query functions correctly:
select lastname as name from person where lastname ='Smith'
union
select firstname as name from person where firstname = 'John'
order by name;
---------------------------------------------------------------------------
The following query generates an Error:
(ERROR: ORDER BY on a UNION/INTERSECT/EXCEPT result must be on one of
the result columns SQL state: 0A000)
select lastname as name from person where lastname ='Smith'
union
select firstname as name from person where firstname = 'John'
order by upper(name);
I would have thought that if the first query worked this query should
have worked. The documentation for 8.2.3 indicated that the order by
would function on an expression when used with a union.
---------------------------------------------------------------------------
WorkAround:
select * from
(select lastname as name from person where lastname ='Smith'
union
select firstname as name from person where firstname = 'John') as
whatever
order by upper(name);
Thanks