CODEWITHSUNDEEP
snowflake-cloud-data-platform
Py1996
Py1996

Reputation: 239

Drop duplicates based on Two distinct column values in snowflake table

I am trying to delete the duplicate rows based on two columns. An example for table as below

Table Name as REFF_TABLE

LOCATIONID  TICKER  ROW_KEY
AB            PA    201605
AB            PA    201605
AB            PA    201606
AB            PA    201606
DA            PB    201705
DA            PB    201706
DA            PB    201707
DE            PC    201808
DE            PC    201809

I want to remove duplicates rows by considering two columns - LOCATIONID, TICKER . Here need to take maximum value of ROW_KEY

Final output table as below

LOCATIONID  TICKER  ROW_KEY
AB            PA    201606
DA            PB    201707
DE            PC    201809

Please help me to solve this

Upvotes: 1

Views: 2174

Answers (1)

Felipe Hoffa
Felipe Hoffa

Reputation: 59165

A typical SQL GROUP BY should be enough:

select locationid, ticker, max(row_key) row_key
from table
group by locationid, ticker

Upvotes: 2

Related Questions