Solved

SQL Script to find duplicates

Posted on 2016-11-16
16
17 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
  • 8
  • 7
16 Comments
 
LVL 16

Accepted Solution

by:
Pawan Kumar Khowal earned 500 total points
Comment Utility
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 49

Expert Comment

by:Ryan Chong
Comment Utility
try this:

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

Open in new window

0
 

Author Closing Comment

by:jspc
Comment Utility
Awesome! Thank you.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Welcome !!
0
 

Author Comment

by:jspc
Comment Utility
Oh I also need the column ProductCode and Description included too
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
Sorry but I get this error

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

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:jspc
Comment Utility
Thanks.

But this is showing me barcodes with blanks.

I only want to see those duplicate barcodes
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
What does the rnk column mean?
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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
Comment Utility
Great. Thanks for your help. All good.
0
 
LVL 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
Great ! Welcome..
0
 

Author Comment

by:jspc
Comment Utility
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 16

Expert Comment

by:Pawan Kumar Khowal
Comment Utility
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

763 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now