• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 101
  • Last Modified:

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
0
jamesmetcalf74
Asked:
jamesmetcalf74
3 Solutions
 
Terry WoodsIT 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
 
Pawan KumarDatabase 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
 
PortletPaulCommented:
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

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now