ORDER BY in UNION query

Antony Paul wrote:

Hi,
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

How to do this.

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.

--
Richard Huxton
Archonet Ltd

Try

select a.col1 as ename from a
union
select b.othercolumn as ename from b
order by ename

Give the columns you want to order on the same name using the "as XXX"
syntax, and remove the "a." prefix from the order statement.

John Sidney-Woollett

Antony Paul wrote:

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

Thanks Tom it worked.

rgds
Antony Paul