sql to display and delete duplicates

I am trying to create a recordset that displays duplicates and also then a SP to delete them.

The table name is "Atts"

Fields are:

Select * from Atts
where caseid and userid (ARE DUPLICATES).

In other words, I want to see the rows in which caseid and userid are the same in row # 1 and row # 2, althought I want to display that only once, not 2 or 3 times.

Then to clean it up I want to run a SP to delete the duplicate items (or 3 or 4) and leave only one.

Example

Name     |   caseid   |    userid   |   other

Aleks       |  1001      |    323        |   XXXXX
Aleks       |  1001       |    323        |  XXXXX
Maria       |   1001            |    433         |
John          |  4324     |  23233  |   YYYY

The first two are duplicates, I want to display that result once, then the SP to delete the exceeding and leave just one. The example shows one but it would have 4 or 5 entries with the same values under "Caseid"  and "userid"


Delete from Atts
where caseid and userid (ARE DUPLICATE)  --- But only the exceeding ones, so to leave one entry

Hope that makes sense.
LVL 1
AleksAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subramani NCommented:
Here is the SP.
CREATE PROCEDURE SP_DEL_DUPS
AS
BEGIN

SELECT
   DISTINCT caseid,userid
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1

WITH TMP_DUPS AS(
   SELECT caseid, userid, rownum = ROW_NUMBER()OVER(PARTITION BY caseid, userid ORDER BY caseid, userid)
   FROM Atts
)
DELETE FROM TMP_DUPS WHERE rownum > 1

END
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Subramani NCommented:
Here is another example.

http://support.microsoft.com/kb/139444
0
AleksAuthor Commented:
Ill try the SP above see how that works. Will get back to you this weekend.
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

AleksAuthor Commented:
I would also need the sql to display the dups. I tried:

SELECT
   DISTINCT caseid,userid
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1


But that displays the unique ones. I need user to review how many records will be deleted, then to test Ill substract and make sure it was done properly
0
Subramani NCommented:
Try this.
SELECT
   caseid,userid,count(1)-1 as 'RowsToBeDeleted'
FROM
   Atts
GROUP BY
   caseid,userid
HAVING
   COUNT(1) > 1

Open in new window

0
AleksAuthor Commented:
Looks great ! Ill take it the SP will work so Ill assign the points already.
0
AleksAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for amucinobluedot's comment #a40306698
Assisted answer: 250 points for Subramani N's comment #a40306671
Assisted answer: 250 points for Subramani N's comment #a40306718

for the following reason:

:)
0
Subramani NCommented:
You dont have to raise a Close request. You just click on the Comment and select as solution and provide the points, it will be closed automatically and moved from the queue.
0
Subramani NCommented:
Please accept the answer and submit.
0
AleksAuthor Commented:
That was not a direct answer to my specific query.  Before I had a chance to even start working on it I got an answer to my specific request, which is much more useful than a link to a page I could have googled. Not sure if that should get any points. Ill leave that to the EE moderator.
0
AleksAuthor Commented:
Sorry I thought I had already done this.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.