Eirikur Eiriksson (10/22/2016)
patelxx (10/22/2016)
Many Thanks for the reply - it really worked for my results set, please can explain the below section of the code for my understanding:NUMBERED_INSTANCES AS
(
SELECT
SD.ID
,SD.NAME
,ROW_NUMBER() OVER
(
PARTITION BY SD.NAME
ORDER BY SD.ID
) AS RID
FROM SAMPLE_DATE SD
The row_number function enumerates the entries within each partition, restarting the enumeration when entering a new partition values. This means that the first instance of a key or "partition by" value will have the value of one which makes it easy to filter out later in the query.
😎
The reason for using a Common Table Expression (CTE) is that the window functions such as row_number cannot be used in the where clause.
A bonus of using this approach is if you want to delete the duplicates. You can delete directly from the CTE where the row number > 1. It won't apply to all situations where you have duplicates, but it's a good tool to have if you need it.