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:

  1. Duplicate types for a worker where all types have the same date - Remove all type entries but one
  2. 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
  3. 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)
)

Demo of solution

Answers


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.


Need Your Help

Automatic file saving from a DBGrid in Delphi - How Possible

file delphi dbgrid

I want to know whether there is a way to perform an automatic file saving without any dialogs.

Refresh view or remove subviews ios

iphone objective-c ios

I'm trying to make a scene where the user can swipe over the screen to browse between posts. The post can be both an image with text or just a note, and the view is altered depending on which one o...