Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 95
  • Last Modified:

SQL Script to find duplicates

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
jspc
Asked:
jspc
  • 8
  • 7
1 Solution
 
Pawan KumarDatabase ExpertCommented:
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
 
Ryan ChongCommented:
try this:

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

Open in new window

0
 
jspcAuthor Commented:
Awesome! Thank you.
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Pawan KumarDatabase ExpertCommented:
Welcome !!
0
 
jspcAuthor Commented:
Oh I also need the column ProductCode and Description included too
0
 
Pawan KumarDatabase ExpertCommented:
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
 
jspcAuthor Commented:
Sorry but I get this error

Msg 208, Level 16, State 1, Line 1
Invalid object name 'Barcode'.
0
 
Pawan KumarDatabase ExpertCommented:
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
 
jspcAuthor Commented:
Thanks.

But this is showing me barcodes with blanks.

I only want to see those duplicate barcodes
0
 
Pawan KumarDatabase ExpertCommented:
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
 
jspcAuthor Commented:
What does the rnk column mean?
0
 
Pawan KumarDatabase ExpertCommented:
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
 
jspcAuthor Commented:
Great. Thanks for your help. All good.
0
 
Pawan KumarDatabase ExpertCommented:
Great ! Welcome..
0
 
jspcAuthor Commented:
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
 
Pawan KumarDatabase ExpertCommented:
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
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.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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