Removing duplicate values from a table 1

[ronaldmacdonald]

Hello

I have a table as thus:

Col1 Col2 Col3 Col4 Col5 etc
1 Mr JOHN DOE ZZ
2 MR JOHN DOE BB
3 Miss SAM ADAMS CC
4 Mr PAUL SMITH VV


I would like to remove column 1 where Col3 and Col4 are the same but Col2 is different case.
Is there a simple way to achieve this?

Thnaks

Ronald

 

[gmmastros]

There are various methods, but I prefer this one:

Declare @Temp Table(Col1 int,Col2 VarChar(20),Col3 VarChar(20),Col4 VarChar(20),Col5 VarChar(20))

Insert Into @Temp Values(1, 'Mr',   'JOHN','DOE'  ,'ZZ')
Insert Into @Temp Values(2, 'MR',   'JOHN','DOE'  ,'BB')
Insert Into @Temp Values(3, 'Miss', 'SAM' ,'ADAMS','CC')
Insert Into @Temp Values(4, 'Mr',   'PAUL','SMITH','VV')

;With Data As
(
    Select  *,
            Row_Number() Over ([blue][b]Partition By Col2, Col3, Col4 [/b][/blue] [green][b]Order By Col1 DESC[/b][/green]) As RowId
    From    @Temp
)
Delete
From    Data
Where   RowId > 1

Select * From @Temp

A couple things about this:

I Create a table variable named @Temp. This is only so that I can dummy up some data and test functionality. This means you can copy/paste this to a query window and run it without affecting your actual data.

Notice that part in blue. The partition by clause. In this section, you would list the columns that you want to use to identify duplicates.

The part in green ultimately helps to decide which one to keep. Notice that I have DESC in there. This means that the rows per group/partition will be sorted descending on col1 so that the largest col1 will have row number = 1. This allows us to delete everything other than the ones.

Make sense?

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

 

[ronaldmacdonald]

Thanks gmmastros - this worked perfectly for my requirement