| From: | "agharta82(at)gmail(dot)com" <agharta82(at)gmail(dot)com> | 
|---|---|
| To: | pgsql-sql(at)lists(dot)postgresql(dot)org | 
| Subject: | Group by column alias where same-column-name already exists | 
| Date: | 2018-12-11 16:39:51 | 
| Message-ID: | 70aee131-8353-607e-0eff-6fb2b9928cf6@gmail.com | 
| Views: | Whole Thread | Raw Message | Download mbox | Resend email | 
| Thread: | |
| Lists: | pgsql-sql | 
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 test 
to explain my question).
select case when (second_table.c1 = 'X') then '1' else '2' end as c1,
first_table.c2
from (
     select 'A'::text as c1, 'B'::text as c2
) first_table
inner join (
     select 'X'::text as c1
     union
     select 'W'::text as c1
     union
     select 'X'::text as c1
)  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 = '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 ambiguous"
Someone knows a way to solve this?
Not replacing grou by alias with its case and without changing column 
names, 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 
computed (case when.... ) c1 in that case.
Best regards,
Agharta
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Voillequin, Jean-Marc | 2018-12-11 16:51:47 | RE: Group by column alias where same-column-name already exists | 
| Previous Message | David G. Johnston | 2018-12-07 01:31:50 | Re: After dump of 4.2 GB SQL file Equal to hard disk space??? |