Dedup rows with insensitive values in snowflake

Question

I would like to dedup rows with case insensitive values.

original table:
| ID | Name           |
| ---| -------------- |
| 1  | Apple          |
| 2  | Banana         |
| 1  | apple          |

desired output after deduping (keep the lowercase):

| ID | Name           |
| ---| -------------- |      
| 2  | Banana         |
| 1  | apple          |

Following statement only works for case sensitive match.

create table DELETE2 as select distinct * from DELETE1;
drop table DELETE1;
alter table DELETE2 rename to DELETE1;

I tried to use following statement, but it did not work.

ALTER SESSION SET QUOTED_IDENTIFIERS_IGNORE_CASE = TRUE;

Thank you! knozawa

Answer 1

You could group by lower(x):

select id, max(name) name
from table
group by 1, lower(name)