Drop duplicates based on Two distinct column values in snowflake table

Question

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

Answer 1

A typical SQL GROUP BY should be enough:

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