ORDER BY with UNION

From: "Niederland" <niederland(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: ORDER BY with UNION
Date: 2007-04-10 22:39:23
Message-ID: 1176244763.500909.18450@n76g2000hsh.googlegroups.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Browse pgsql-general by date

  From Date Subject
Next Message Marc 2007-04-11 05:45:44 Acces via applets
Previous Message Tom Lane 2007-04-10 21:42:46 Re: plperl "set-valued function" problem