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
Advertisement
Answer
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