How to Delete Duplicate Record - Bandi [Resolved]

Posted by Jayakumars under Sql Server on 4/7/2015 | Points: 10 | Views : 340 | Status : [Member] [MVP] | Replies : 3
hi

How to Delete Duplicate Record Match StudentId,ClassId 2 fields

Create Table TestNameList
(
StudentId int,
StudName varchar(50),
ClassId int
)


Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',2)
Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',3)
Insert into TestNameList values(3,'John',3)
Insert into TestNameList values(3,'Joshn',3)

Here I need How to find Duplicate Record match StudentId and ClassId Allow 1 Records then
Duplicate Record need to Delete

Mark as Answer if its helpful to you


Responses

Posted by: Bandi on: 4/7/2015 [Member] [MVP] Platinum | Points: 50

Up
0
Down

Resolved
--How to Delete Duplicate Record Match StudentId,ClassId 2 fields
Create Table TestNameList
(
StudentId int,
StudName varchar(50),
ClassId int
)


Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',2)
Insert into TestNameList values(1,'John',1)
Insert into TestNameList values(2,'John',3)
Insert into TestNameList values(3,'John',3)
Insert into TestNameList values(3,'Joshn',3)

;WITH DelDups  AS (SELECT ROW_NUMBER() OVER( PARTITION BY StudentId, ClassId ORDER BY StudentID) Rn FROM TestNameList )
DELETE FROM DelDups WHERE Rn>1


SELECT * FROM TestNameList

DROP TABLE TestNameList

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Jayakumars on: 4/7/2015 [Member] [MVP] Bronze | Points: 25

Up
0
Down
Hi
Thanks your reply bandi
after i execute this query i saw this output but this is wrong also given here duplidate

studentid name classid
2 John 2
1 John 1
2 John 3
3 John 3

look here also student id comes 2 times


Mark as Answer if its helpful to you

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Bandi on: 4/7/2015 [Member] [MVP] Platinum | Points: 25

Up
0
Down
studentid name classid
2 John 2
1 John 1
2 John 3
3 John 3


How to find Duplicate Record match StudentId and ClassId means that we are checking for duplicate records based on StudentID and ClassID. Here, we got StudentID 2 twice because the combination of StudentID and ClassID is unique. ( 2,3 and 2,2)

If you want to get duplicate records based on StudentID, then modify SELECT statement as follows:
SELECT ROW_NUMBER() OVER( PARTITION BY StudentId ORDER BY StudentID) Rn

Mark This Response as Answer
--
Chandu
http://www.dotnetfunda.com/images/dnfmvp.gif

Jayakumars, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response