• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 504
  • Last Modified:

Remove Duplicate record using SQL Query

Hi,

I have a sql table where I want to remove duplicate records. Without using temp table, is there a better way to remove duplicates.

I want to remove emp1 from bucket 12 & wants to keep one emp1 in bucket 12. Also I want to remove emp2 from bucket 16 as emp2 exists in bucket12.

Here is my table

Table1
-----------
   bucketNum    empName
      12           emp1
      12           emp2
      12           emp3
      12           emp1
      16           emp2
      16           emp4


Output
------------
 bucketNum    empName    
      12           emp1
      12           emp2
      12           emp3    
      16           emp4


Thanks
0
r_pat72
Asked:
r_pat72
1 Solution
 
appariCommented:
try this, take back up of existing data or run it within a transaction and commit after checking the results.
;with data as (Select *, row_number() over(partition by empName order by bucketNum) rowNum from tableName)
delete from data where rowNum > 1

Open in new window

0
 
BuggyCoderCommented:
here is the list of all possible ways of removing duplicates:-
http://www.simple-talk.com/sql/t-sql-programming/removing-duplicates-from-a-table-in-sql-server/
0
 
ben9Commented:
"remove emp2 from bucket 16 as emp2 exists in bucket12."

What is your criteria for which one to keep? Do you always keep the lower numbered bucket and consider the others duplicates?
0
 
r_pat72Author Commented:
yes, want to keep lower number bucket.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now