Обсуждение: ORDER BY in UNION query

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.

rgds
Antony Paul

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:

> 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.
>
> rgds
> Antony Paul
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

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


On Mon, 10 Jan 2005 08:39:50 -0500, Tom Lane <tgl@sss.pgh.pa.us> 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
>