SQL query to remove duplicates from single column based on latest date

user_12320257 0
Viewed 164

I have a table where I have duplicate values in just one column, and I want to remove those value using the column which has timestamp values. So the value which has latest timestamp should be in the expected result.

For example, using the below table column1 is varchar, column2 is timestamp

COLUMN1  COLUMN2 
ABC      1-2-2021
ABC      2-2-2021
JKL      16-4-2021  
JKL      15-4-2021

Looking at the above table I have two same values ABC but only the second one should be in the result because it has the latest timestamp value then the first, similar case with the JKL values.

Expected result

COLUMN1  COLUMN2 
ABC      2-2-2021
JKL      16-4-2021

I have tried a few partitioning sql queries and also CTE but not able to get the desired result. Any help would be appreciated. Thanks

snowflake-cloud-data-platform sql database
1 Answers

I have tried a few partitioning sql queries and also CTE but not able to get the desired result

Using QUALIFY it could be achieved without cte:

SELECT *
FROM tab
QUALIFY ROW_NUMBER() OVER(PARTITION BY COLUMN1  ORDER BY COLUMN2 DESC) = 1
user_50708790
Related