Duplicate Records - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Duplicate Records

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2008-12-04 : 15:03:15

Hi.I wrote a Query to show the duplicate records:

SELECT PartNumber, COUNT(*) AS Expr1
FROM tbl_InvQtys
GROUP BY PartNumber
HAVING (COUNT(*) > 1)

which is fine.but I have another column named TAG that I want to see
it on the result.when I change the query to:

SELECT PartNumber,TAG, COUNT(*) AS Expr1
FROM tbl_InvQtys
GROUP BY PartNumber,TAG
HAVING (COUNT(*) > 1)

Im getting no result for that.
how can I see the duplicate records with more than 2 column?
thank you in advance

revdnrdy
Posting Yak Master

220 Posts

Posted - 2008-12-04 : 15:13:31

Try this. (coded on MSSQL Server 2005)

SELECT PartNumber,TAG FROM tbl_InvQtys
WHERE Partnumber
IN (SELECT partnumber
FROM tbl_InvQtys
GROUP BY partnumber
HAVING Count(*) >1)
ORDER BY PartNumber

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-05 : 03:10:50

if sql 2005 you can use this also


SELECT PartNumber,TAG
FROM
(
SELECT PartNumber,TAG,COUNT(*) OVER (PARTITION BY PartNumber) AS Cnt 
FROM tbl_InvQtys
)t
WHERE Cnt>1

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources