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

Question

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

Answer 1

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