Lists: | pgsql-general |
---|
From: | Antony Paul <antonypaul24(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | ORDER BY in UNION query |
Date: | 2005-01-10 12:13:31 |
Message-ID: | 2989532e0501100413665c136c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Richard Huxton <dev(at)archonet(dot)com> |
---|---|
To: | Antony Paul <antonypaul24(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY in UNION query |
Date: | 2005-01-10 12:58:26 |
Message-ID: | 41E27BF2.8080701@archonet.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | John Sidney-Woollett <johnsw(at)wardbrook(dot)com> |
---|---|
To: | Antony Paul <antonypaul24(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY in UNION query |
Date: | 2005-01-10 13:10:42 |
Message-ID: | 41E27ED2.1080203@wardbrook.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)postgresql(dot)org
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Richard Huxton <dev(at)archonet(dot)com> |
Cc: | Antony Paul <antonypaul24(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY in UNION query |
Date: | 2005-01-10 13:39:50 |
Message-ID: | 11160.1105364390@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Antony Paul <antonypaul24(at)gmail(dot)com> |
---|---|
To: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: ORDER BY in UNION query |
Date: | 2005-01-17 11:59:19 |
Message-ID: | 2989532e050117035939848106@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
>