Subject: Re: Group by column alias where same-column-name already exists
To: "Voillequin, Jean-Marc" <Jean-Marc.Voillequin@moodys.com>,
 "pgsql-sql@lists.postgresql.org" <pgsql-sql@lists.postgresql.org>
References: <70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com>
 <1EC8157EB499BF459A516ADCF135ADCE3A00A7E5@LON-WGMSX712.ad.moodys.net>
From: "agharta82@gmail.com" <agharta82@gmail.com>
Message-ID: <d2f400b9-09e7-dc9b-0835-5c402dda1c38@gmail.com>
Disposition-Notification-To: "agharta82@gmail.com" <agharta82@gmail.com>
Date: Wed, 12 Dec 2018 10:49:46 +0100
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.3.1
MIME-Version: 1.0
In-Reply-To: 
 <1EC8157EB499BF459A516ADCF135ADCE3A00A7E5@LON-WGMSX712.ad.moodys.net>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Language: it-IT
Precedence: bulk

Nice solution!

Thank you so mutch!

Il 11/12/18 17:51, Voillequin, Jean-Marc ha scritto:
> "...group by 1, first_table.c2"
>
> should do the trick.
>
> -----Original Message-----
> From: agharta82@gmail.com [mailto:agharta82@gmail.com]
> Sent: Tuesday, December 11, 2018 5:40 PM
> To: pgsql-sql@lists.postgresql.org
> Subject: Group by column alias where same-column-name already exists
>
>  =20
>
> CAUTION: This email originated from outside of Moody's. Do not click li=
nks or open attachments unless you recognize the sender and know the cont=
ent is safe.
>
>  =20
>
> Hi all,
>
> A little question about grouping by a computed column alias where other=
 columns with same name exists.
>
> Take look at this query (don't take care about, it was created as a tes=
t to explain my question).
>
> select case when (second_table.c1 =3D 'X') then '1' else '2' end as c1,=

> first_table.c2
> from (
>   =C2=A0=C2=A0=C2=A0 select 'A'::text as c1, 'B'::text as c2
> ) first_table
> inner join (
>   =C2=A0=C2=A0=C2=A0 select 'X'::text as c1
>   =C2=A0=C2=A0=C2=A0 union
>   =C2=A0=C2=A0=C2=A0 select 'W'::text as c1
>   =C2=A0=C2=A0=C2=A0 union
>   =C2=A0=C2=A0=C2=A0 select 'X'::text as c1
> )=C2=A0 second_table on (true)
> group by c1, first_table.c2
>
>
> I have a computed coulmn alias in select called c1 (select case when
> (second_table.c1 =3D 'X') then '1' else '2' end as c1)
>
> I want to group by that alias c1 (group by c1)
>
> BUT first_table has a c1 column and second_table has a c1 column too!
>
> If i run the query it returns "ERROR: column reference "c1" is ambiguou=
s"
>
> Someone knows a way to solve this?
>
> Not replacing grou by alias with its case and without changing column n=
ames, oblivious.
>
> In other dbs (like firebird) main (computed ) select column alias name =
takes precedence in group by clause. So if i group by c1 that means the c=
omputed (case when.... ) c1 in that case.
>
>
> Best regards,
>
> Agharta
>
>
>
> -----------------------------------------
>
> Moody's monitors email communications through its networks for regulato=
ry compliance purposes and to protect its customers, employees and busine=
ss and where allowed to do so by applicable law. The information containe=
d in this e-mail message, and any attachment thereto, is confidential and=
 may not be disclosed without our express permission. If you are not the =
intended recipient or an employee or agent responsible for delivering thi=
s message to the intended recipient, you are hereby notified that you hav=
e received this message in error and that any review, dissemination, dist=
ribution or copying of this message, or any attachment thereto, in whole =
or in part, is strictly prohibited. If you have received this message in =
error, please immediately notify us by telephone, fax or e-mail and delet=
e the message and all of its attachments. Every effort is made to keep ou=
r network free from viruses. You should, however, review this e-mail mess=
age, as well as any attachment thereto, for viruses. We take no responsib=
ility and have no liability for any computer virus which may be transferr=
ed via this e-mail message.
>
> This email was sent to you by Moody=E2=80=99s Investors Service EMEA Li=
mited
> Registered office address:
> One Canada Square
> Canary Wharf
> London, E14 5FA
> Registered in England and Wales No: 8922701
>
> -----------------------------------------