duplicates problem - 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

SQL Server 2000 Forums

Transact-SQL (2000)

duplicates problem

Author

Topic

mikejohnson
Posting Yak Master

153 Posts

Posted - 2004-01-28 : 11:20:26

i want to show my users all the duplicates in the database. here is my query so far:

SELECT SAP, PromoID, WeekEnding, COUNT(*) AS DuplicateCount, Units
FROM Units
GROUP BY SAP, PromoID, WeekEnding, Units
HAVING (COUNT(*) > 1)

the problem i have is right now it shows only one of the records contained within the duplicates. i want it to show all of them. for example, in my database i have these 3 records:

sap promoid weekending units
--- ------- ---------- -----
1 1 1/1/03 1
1 1 1/1/03 1

right now it only shows one record with that data, i need it to show both, how do i do that?

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-01-28 : 12:03:14

How about:

SELECT *
FROM Units U
JOIN (
SELECT SAP, PromoID, WeekEnding, Units, COUNT(*) AS DuplicateCount
FROM Units
GROUP BY SAP, PromoID, WeekEnding, Units
HAVING (COUNT(*) > 1) ) as d
ON U.SAP = d.SAP
AND U.PromoID = d.PromoID
AND U.WeekEnding = d.WeekEnding
AND U.Units = d.Units

===============================================
Creating tomorrow's legacy systems today.
One crisis at a time.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources