Duplicate Field 2

[fiber0pti]

I have a field called PartNumber. I know there a duplicates in it and I am wanting to create SQL query that returns those duplicates. I was looking around and it seems that everyone just wants to know what the duplicates are and not actually list the duplicate rows with a RecordID or Unique ID. Can someone help me?

 

[fiber0pti]

Anyone know how to do this?

 

[SQLSister]

I couldn't get it to work with one SQL statement, so I created a view that identified all the parts with duplicates and then wrote a query joining the view and the table to get the fields I wanted.

Code to create the view:
CREATE VIEW dbo.VIEW1
AS
SELECT PartNumber
FROM dbo.PRICE
GROUP BY PartNumber
HAVING (COUNT(PartNumber) > 1)

Final Query code:
SELECT PRICE.*
FROM PRICE INNER JOIN
VIEW1 ON PRICE.PartNumber = VIEW1.PartNumber
ORDER BY PRICE.PartNumber

 

[guest2]

Hello,
Here is one way you could put it all into one SQL statement.

Select * From Price
Where PartNumber In
(Select PartNumber From Price Group By PartNumber Having Count(PartNumber) > 1)
Order By Price.PartNumber

 

[sunila7]

Hi,

Try a Query similar to this... I have put some random field names... u might have to correct it

SELECT * FROM PartsTable P
INNER JOIN
(SELECT PartID FROM
PartsTable
Group BY PartID
Having Count(PartNumber) > 1) CntTBL ON CntTBL.PartId = P.PartID


Sunil