removing dups by min dates keeping the max 1

[ciarra41]

I’m trying to remove duplicates records ids keeping the max date and deleting the min.

Delete from mytable
where id=(select a.id, MIN(CONVERT(varchar(12),a.dates,101)) from mytable as a where a.id=mytable.id
and id IN(select id, count(*) rcrds from mytable group by id HAVING count(id) >1 ) group by a.id)

 

[gmmastros]

Can you show some sample data and expected results?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ciarra41]

start
id dates
244547 4/1/2011
244547 4/12/2011
645877 4/24/2011
645877 4/4/2011
588478 4/5/2011
588478 4/16/2011
588478 4/17/2011
854778 4/17/2011
854778 4/8/2011
325411 4/9/2011
325411 4/10/2011

end
244547 4/12/2011
645877 4/24/2011
588478 4/17/2011
854778 4/17/2011
325411 4/10/2011

 

[gmmastros]

I would approach this query by first writing a query to return the data you want to keep. This query is relatively simple to write.

Select ID, Max(Dates) As MaxDate
From   YourTableName
Group By ID

Now, if we join this data back to the original table and remove all rows (from the original table) where the data does not match, we would be all set, right?

Let's write this as a select just so we can see what is happening.

Select *
From   YourTableNameHere T
       Left Join (
        Select ID, Max(Dates) As MaxDate
        From   YourTableNameHere
        Group BY ID
        ) As A
       On T.ID = A.ID
       And T.Dates = A.MaxDate

Notice that there are 4 columns returned from this query. You get 2 columns from the original table and an additional 2 columns from the derived table. Also notice that we want to delete the rows from the original query where the data in the 3rd and fourth column is null.

Delete T
From   YourTableNameHere T
       Left Join (
        Select ID, Max(Dates) As MaxDate
        From   YourTableNameHere
        Group BY ID
        ) As A
       On T.ID = A.ID
       And T.Dates = A.MaxDate
Where  A.Id Is NULL

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ciarra41]

yes thanks for you help.