I tried using row_number() to select the records having row_number as 1 and inserted into separate table. Is there any other method on deleting from the same table without using another table?
1 Answers
Tried the hash approach but snowflake didn't work ... here's an alternative just replace the table with select distinct * from table!
Step 1 - create some sample data (ideally anonymous would have supplied this)
create or replace table arr_base(account_id varchar, account_name varchar, activity_date date, arr number(32,4));
insert into arr_base (account_id, account_name, activity_date, arr)
values
('A','ACCOUNT A','2021-01-31',50)
,('B','ACCOUNT B','2021-01-31',40)
,('A','ACCOUNT A','2020-01-31',40)
,('B','ACCOUNT B','2020-01-31', 35)
,('C','ACCOUNT C','2020-01-31', 30)
,('D','ACCOUNT D','2020-01-31', 33)
, ('A','ACCOUNT A','2021-01-31',50)
,('B','ACCOUNT B','2021-01-31',40)
,('A','ACCOUNT A','2020-01-31',40)
,('B','ACCOUNT B','2020-01-31', 35)
,('C','ACCOUNT C','2020-01-31', 30)
,('D','ACCOUNT D','2020-01-31', 30);
Step 2 - Validate table
Step 3 -
replace table arr_base as select distinct * from arr_base
Related