Delete Duplicate records in a file

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Delete Duplicate records in a file

Author

Topic

divan
Posting Yak Master

153 Posts

Posted - 2012-08-31 : 10:04:01

I have the following fields in a table

number, client_number,first_name,last_name,amount

example of data

TX 11240 13 NULL Sad Clinic 313307
TX 11240 13 NULL Sad Clinic 341523
TX 10762 17 NULL North 22212
TX 11240 69 NULL ler Clinic 313307
TX 11240 69 NULL ler Clinic 341523
TX 10636 83 Bernardino Abaya 12538
TX 10184 92 iya Ahm 7603
TX 11920 139 Elzbieta Baranowska-Daca 0
TX 11540 140 ernard Barrett 17649

I am using the folling script to remove the duplicates

DELETE
FROM #TEMP1
WHERE POLICY_NUMBER NOT IN
(
SELECT MAX(POLICY_NUMBER)
FROM #TEMP1
GROUP BY
CLIENT_NUMBER,LAST_NAME)

SELECT * FROM #TEMP1

expecting the following

TX 11240 13 NULL Sad Clinic 341523
TX 10762 17 NULL North 22212
TX 11240 69 NULL ler Clinic 341523
TX 10636 83 Bernardino Abaya 12538
TX 10184 92 iya Ahm 7603
TX 11920 139 Elzbieta Baranowska-Daca 0
TX 11540 140 ernard Barrett 17649

but instead it is not removing anything...

Any suggestions???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:06:52

so you always want to retain record with largest amount for each group?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

divan
Posting Yak Master

153 Posts

Posted - 2012-08-31 : 10:11:07

Yes..or just removing the duplicate it really does not matter...

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-08-31 : 10:15:20

declare @t table (i int, j int)
insert @t select 1,1
insert @t select 1,1
insert @t select 2,1
insert @t select 2,1
insert @t select 2,1
insert @t select 3,1

; with cte as
(select i, seq = ROW_NUMBER() over (partition by i order by j) from @t)
delete cte
where seq <> 1

select * from @t

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-31 : 10:16:43

[code]
DELETE t
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY number, client_number ORDER BY amount DESC) AS Seq,*
FROM table
)t
WHERE Seq>1
[/code]

suggest you to first put a select and check the records and then do delete once staisfied with result

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources