Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


filter duplicate students via T-SQL


filter duplicate students via T-SQL

Author
Message
sdhanpaul
sdhanpaul
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 566
first of all naming a column and the table "Students is no good!
, this gave me a duplicate and counted the number of duplicates

SELECT     Student, IDNo, Tel3,Tel1, COUNT(*) AS dupes  
FROM dbo.Student
GROUP BY Student, IDNo, Tel3,Tel1
HAVING (COUNT(*) > 1)



now if u need to find the duplicates and delete one of them:

/* Delete Duplicate records */
WITH CTE (Student, IDNo, Tel3,Tel1, DuplicateCount)
AS
(
SELECT [Student], IDNo, Tel3,Tel1,
ROW_NUMBER() OVER(PARTITION BY Student, IDNo, Tel3,Tel1 ORDER BY Student) AS DuplicateCount
FROM dbo.Student
)
DELETE
FROM CTE
WHERE DuplicateCount > 1



problem solved...


i may be going on a wild thing here ... and cant seem to understand your problem well... but i dont know..
sdhanpaul
sdhanpaul
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 Visits: 566
i have a problem with using numbers like 1111, 8888, 7777 etc in that sort field... whats the point of limiting a "sort" field to these numbers? i dont get it.
geoff5
geoff5
SSC-Enthusiastic
SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)SSC-Enthusiastic (188 reputation)

Group: General Forum Members
Points: 188 Visits: 543
sdhanpaul,

The problem with the "group by" approach you propose is that it defines a duplicate row as that shares values in all the columns Student AND IDNo AND Tel3 AND Tel1. This does not meet the definition of a duplicate row stated in the original post, which is that a duplicate row is one that shares values in Student AND (IDNo OR Tel3 OR Tel1). To handle the latter definition of "duplicate" is much more complex than a basic GROUP BY clause can address.

The situation is further complicated by the fact that the value in Tel1 in one row (which I presume to be a telephone number in the actual real-world data) could be stored as Tel3 in another row and vice versa. So the test of "duplicate" requires checking not just the values within each of these columns but also between these two columns on different rows.

I think the solution I posted earlier addresses these concerns. I welcome your feedback, if you test it and find that it does not.
Sergiy
Sergiy
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5852 Visits: 11417
geoff5 (4/24/2013)
I think the solution I posted earlier addresses these concerns. I welcome your feedback, if you test it and find that it does not.

That would be for Kevin to answer...
But he's gone and we're not getting to a solution of this riddle.
Such a pity...
Crying
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search