Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

SQL Script to find duplicates

Posted on 2016-11-16
16
Medium Priority
?
65 Views
Last Modified: 2016-11-23
Hello,

I have a SQL Database with a table called Products.
Within this table I have a barcode column called ‘Barcode’

I’m want to run a script which will show me all those barcodes which are duplicate.

Does anyone know how to achieve this?

Thank you.
0
Comment
Question by:jspc
[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
  • 8
  • 7
16 Comments
 
LVL 32

Accepted Solution

by:
Pawan Kumar earned 2000 total points
ID: 41890861
Try..

SELECT * FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY (SELECT 1)) rnk 
	FROM Barcode
)k
WHERE k.rnk > 1

Open in new window


Or this

SELECT Barcode , COUNT(*) DuplicateCount FROM Products
GROUP BY Barcode 
HAVING COUNT(*) > 1

Open in new window


Hope it helps !!
0
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 41890863
try this:

select Barcode
from Products
group by Barcode
having count(Barcode) > 1

Open in new window

0
 

Author Closing Comment

by:jspc
ID: 41890867
Awesome! Thank you.
0
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890868
Welcome !!
0
 

Author Comment

by:jspc
ID: 41890871
Oh I also need the column ProductCode and Description included too
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890874
In below you will get all columns , Just replace * with the columns you want.

SELECT * FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY (SELECT 1)) rnk 
	FROM Barcode
)k
WHERE k.rnk > 1

Open in new window

0
 

Author Comment

by:jspc
ID: 41890876
Sorry but I get this error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Barcode'.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890877
Ohh try...

SELECT * FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY (SELECT 1)) rnk 
	FROM Products
)k
WHERE k.rnk > 1

Open in new window

0
 

Author Comment

by:jspc
ID: 41890882
Thanks.

But this is showing me barcodes with blanks.

I only want to see those duplicate barcodes
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890884
Ok , Try this... If you dont want blank values..

SELECT * FROM 
(
	SELECT * , ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY (SELECT 1)) rnk 
	FROM Products
        WHERE LEN(Barcode) > 0
)k
WHERE k.rnk > 1

Open in new window

0
 

Author Comment

by:jspc
ID: 41890885
What does the rnk column mean?
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890889
I have generated a new column called rnk, which will give us ranking. So if the barcode is duplicate its value will be > 1.

You can ignore it while selecting the final data like bold below.. Here replace * with your columnNames.

SELECT * FROM
(
      SELECT * , ROW_NUMBER() OVER (PARTITION BY Barcode ORDER BY (SELECT 1)) rnk
      FROM Products
        WHERE LEN(Barcode) > 0
)k
WHERE k.rnk > 1
0
 

Author Comment

by:jspc
ID: 41890890
Great. Thanks for your help. All good.
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41890891
Great ! Welcome..
0
 

Author Comment

by:jspc
ID: 41900059
Hello, in regards to the above answer this doesn't show me duplicate product code.

What we are wanting are the two items that have the same barcode, so we can sort out which one belongs to who.
Your query only shows one item and it’s barcode, not both items with the same barcode.  
I have already found an item on your list that shows the barcode, but it is the correct barcode for that item – so where’s the other item showing the same barcode number?
0
 
LVL 32

Expert Comment

by:Pawan Kumar
ID: 41900074
Only for duplicates...


SELECT * FROM 
(
	SELECT * , COUNT(*) OVER (PARTITION BY Barcode) rnk 
	FROM Products
        WHERE LEN(Barcode) > 0
)k
WHERE k.rnk > 1

Open in new window

0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

618 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