how to remove duplicates irecords in a table

Question

how to remove duplicates irecords in a table

PRAMANA.DBA

SSCertifiable

Points: 5507
More actions
February 12, 2011 at 2:08 am

#245118

i have one table below like that,this table dont have any constraints.
create table Emp(Empno int,Ename varchar(100),Job varchar(100),Mgr int,HireDate datetime,Sal money,Comm money,Deptno int)
i inserted values in 5 times
values(1001,'Suresh','President',NULL,'01/01/78',5000,NULL,10)
Insert into Emp values(1002,'Ramesh','Manafer',1001,'01/01/78',4000,NULL,20)
Insert into Emp values(1003,'Ravi','Manager',1001,'01/01/78',3500,NULL,30)
Insert into Emp values(1004,'Vijay','Manager',1001,'01/01/78',4000,NULL,40)
Insert into Emp values(1005,'Ajay','Salesman',1003,'02/04/79',3000,NULL,50)
select * from Emp
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL101
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL202
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL303
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL404
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL105
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL206
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL307
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL408
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL109
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL2010
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL3011
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL4012
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL1013
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL2014
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL3015
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL4016
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL1017
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL2018
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL3019
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL4020
1001SureshPresidentNULL1978-01-01 00:00:00.0005000.00NULL1021
1002RameshManager10011978-01-01 00:00:00.0004000.00NULL2022
1003RaviManager10011978-01-01 00:00:00.0003500.00NULL3023
1004VijayManager10011978-01-01 00:00:00.0004000.00NULL4024
how i can remove duplicates in that.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004433 More actions · Answer 1

This is a very common and easily searched problem. In this case, Google is your friend...

http://www.google.com/search?hl=en&source=hp&biw=1276&bih=561&q=sql+server+remove+duplicates&aq=0&aqi=g10&aql=f&oq=SQL+SERVER+REMOVE+Duplicates

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

ramana.palakolanu

i inserted values in 5 times
values(1001,'Suresh','President',NULL,'01/01/78',5000,NULL,10)
Insert into Emp values(1002,'Ramesh','Manafer',1001,'01/01/78',4000,NULL,20)
Insert into Emp values(1003,'Ravi','Manager',1001,'01/01/78',3500,NULL,30)
Insert into Emp values(1004,'Vijay','Manager',1001,'01/01/78',4000,NULL,40)
Insert into Emp values(1005,'Ajay','Salesman',1003,'02/04/79',3000,NULL,50)

If you did insert as you stated - "5 times" how come when counting the rows returned by your "select * from Emp" I count 6 entries for every insert except for:

Insert into Emp values(1005,'Ajay','Salesman',1003,'02/04/79',3000,NULL,50)

which does not appear even 1 time in the results of that "select * from Emp" statment?

and this line "values(1001,'Suresh','President',NULL,'01/01/78',5000,NULL,10)" would not be enered at all in fact it should have given you an error message.

Now I modified your INSERT INTO statements so as to insert values for(1005,'Ajay','Salesman',1003,'02/04/79',3000,NULL,50) only once and the others 3 times for each entry, into a temp table which I named #Emp. (This only to make it easier for me to clean up afterwards by simple closing my SSMS window and something you should consider using when testing the code.)

With all that said, before you post a question again please, please, click on the first link in my signature block and follow the directions and the sample T-SQL code (Included in the article) to post table definitions, sample data in a format easy to use so those that want to assist you can do so easily. Remember all those who do assist you are NOT paid to do so, but do so only as a gesture of good will and the effor to help others.

Now

;with cte

as (select row_number() over(partition by Empno,Ename,Job order by Job) as rn,

Empno,Ename,Job

from #Emp)

SELECT * FROM cte -- for testing only

Replace the select * with

"DELETE FROM cte WHERE rn > 1" - to delete the duplicate entries.

Result:

rnEmpnoEnameJob

11001SureshPresident

21001SureshPresident

31001SureshPresident

11002RameshManafer

21002RameshManafer

31002RameshManafer

11003RaviManager

21003RaviManager

31003RaviManager

11004VijayManager

21004VijayManager

31004VijayManager

11005AjaySalesman

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]