How to Delete Duplicate Record - Bandi [Resolved]

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

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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

Kumaraspcode2009@gmail.com

[Resolved]

Reply | Reply with Attachment

Alert Moderator

Responses

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

0	--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

--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

0	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 Kumaraspcode2009@gmail.com Jayakumars, if this helps please login to Mark As Answer. \| Alert Moderator

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

0	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

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

Latest Posts