Re: ORDER BY in UNION query

Richard Huxton <dev@archonet.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