Solved

Identifying duplicates

Posted on 2016-09-25
3
76 Views
Last Modified: 2016-09-26
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
Comment
Question by:jamesmetcalf74
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 35

Assisted Solution

by:Terry Woods
Terry Woods earned 125 total points
ID: 41815098
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
 
LVL 29

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41815193
@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
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 125 total points
ID: 41815196
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question