Identifying duplicates

whats the easiest way to identify duplicates in the results of a sql query..
can you do it any other way besides organizing the results and then finding them visually
jamesmetcalf74Asked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
@Author - there are multiple options for this.

One of the option is to use left outer join.

CREATE TABLE DeleteDuplicateUsingOuterJoin
(
    ID SMALLINT
)
GO
 
INSERT INTO DeleteDuplicateUsingOuterJoin
VALUES
(1),
(2),
(2),
(3),
(3),
(3)
GO

;WITH CTE AS
(
    SELECT * , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) rnk FROM DeleteDuplicateUsingOuterJoin
)
DELETE p
FROM CTE p
LEFT OUTER JOIN
(
    SELECT MIN(rnk) ID FROM CTE
    GROUP BY ID
)u ON u.ID = p.rnk
WHERE u.ID IS NULL

Open in new window




If you want to delete duplicate then use below-

@Author - there are multiple options for this. One of the option is

--CREATE TABLE
CREATE TABLE DeleteDup
(
ID INT
)
Go
 
--Insert Data
INSERT INTO DeleteDup VALUES(1),(2),(1)
 
--Verify Data
SELECT ID FROM DeleteDup

--
 
/************   SOLUTION 1         ****************/
 
 
 
WITH CTE AS
(
       SELECT *, ROW_NUMBER() OVER ( PARTITION BY ID ORDER BY ID ) RNK FROM DeleteDup    
)
DELETE FROM CTE WHERE RNK > 1
  
 
--

Open in new window

0
 
Terry WoodsConnect With a Mentor IT GuruCommented:
Duplicate of one value (ie in one column), or multiple columns, or entire rows?

It can be done like this:
select col1, col2, count(*)
from my_table
group by col1, col2 having count(*)>1

Open in new window

1
 
PortletPaulConnect With a Mentor freelancerCommented:
Terry's answer above is a good common method.

Here is a EE article of the topic you may find helpful
https://www.experts-exchange.com/articles/3379/deleting-duplicate-records-in-a-database-without-a-primary-key.html

and note this comment under that article:
Deleting duplicates can be hard work, and I think you have covered off the more popular approaches pretty well.

I also like the importance of taking a backup first :)
0
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.

All Courses

From novice to tech pro — start learning today.