SQL: Remove duplicates (slightly different)
I asked a question similar to this one here and was given great answers, but it turns out the following way is the way it needs to be done. I've been working on this for several hours now and haven't been able to make too much progress.
How do I remove duplicates from a table that is set up in the following way?
unique_ID | worker_ID | date | type_ID
A worker can have multiple type_ID's associated with them. I want to remove under the following conditions:
- Duplicate types for a worker where all types have the same date - Remove all type entries but one
- Duplicate types for a worker where one type has a date and the other has a null value - Remove the type entry/entries with the null value
- Duplicate types for a worker where both have null date values - Remove all type entries but one
Number 2 is the only one I'm having an issue with. I think I've got 1 and 3 working.
My code for number 1 per request.
Delete from TABLENAME WHERE unique_ID IN ( SELECT MAX(unique_ID) FROM TABLENAME GROUP BY worker_ID, type_ID, date HAVING COUNT(*) > 1 AND date IS NOT NULL )
I figured number three should be a small alteration of the code used for number 1.
UPDATE (12/8/2011) - Solution I came up with for condition 2
select unique_ID, worker_ID, type_ID into #validActive from #tbl where date is not null select unique_ID, worker_ID, type_ID into #nullActive from #tbl where date is null Delete from #tbl where unique_ID in ( select #nullActive.unique_ID from #validActive join #nullActive on #validActive.worker_ID = #nullActive.worker_ID where (#validActive.worker_ID = #nullActive.worker_ID and #validActive.type_ID = #nullActive.type_ID) )
with cte as ( select unique_ID, worker_ID, [date], type_ID, row_number() over (partition by worker_id order by isnull([date], '1753-01-01') desc) as [rn] from your_table ) delete cte where [rn] <> 1
You should be able to adapt this solution to your other cases as well.