CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

Create a separate table based on select condition query in snowflake

I am using select query with condition to remove the duplicates. Query as below

select * from (
select LOCATIONID, OBSERVATION_TIME_UTC, max(ROW_KEY) ROW_KEY from OLD_TABLE group by LOCATIONID, OBSERVATION_TIME_UTC
)

here it will display only 3 columns and LOCATIONID, OBSERVATION_TIME_UTC,ROW_KEY out of 15 columns

I want to create a separate table which has all the columns and order of the columns should not be changed.

I tried below query

create or replace table NEW_TABLE as
select * from (
select LOCATIONID, OBSERVATION_TIME_UTC, max(ROW_KEY) ROW_KEY from OLD_TABLE group by LOCATIONID, OBSERVATION_TIME_UTC
)

but the above query gives only 3 columns, whereas I need the data as it is in new table(it should have all the columns). could someone correct my query please!

Upvotes: 1

Views: 514

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175556

Qualify could be used to grab the highest row(row_key) per location and observation_time:

 -- create or replace new_table as
Select *
From old_table
Qualify row_number() over(partition by location_id, observation_time_utc 
                          order by row_key desc) = 1

Upvotes: 1

Related Questions