Re: ORDER BY in UNION query

Thanks Tom it worked.

rgds
Antony Paul

On Mon, 10 Jan 2005 08:39:50 -0500, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> Richard Huxton <dev(at)archonet(dot)com> writes:
> > Antony Paul wrote:
> >> I need to use ORDER BY clause in a UNION query and the Order BY
> >> columns are not included in the SELECT statement. I tried like this
> >>
> >> (select .... from a) UNION (select ..... from b) order by a.ename;
> >>
> >> It says that
> >> ERROR: Attribute "ename" not found
>
> > The "order by" is applying to the results of the union, not one of the
> > sub-selects. If you want to sort by a value, you'll need to include it
> > in the results list.
>
> You could suppress the order-by fields after the fact:
>
> SELECT x,y,z FROM
> ( (SELECT x,y,z,q FROM a)
> UNION
> (SELECT x,y,z,q FROM b)
> ORDER BY q
> ) ss;
>
> Also, always ask yourself if you really need UNION or if UNION ALL
> is sufficient. Removing duplicates from a large UNION is *expensive*,
> and all too often a waste of time.
>
> regards, tom lane
>