Find duplicates

[Raul2005]

ID Account Client DealerNam MSEG Tier
01 3949 ALLIUK ABN S_HG 3
02 3949 ALLIUK ABN S_HG 3
03 3949 ALLIUK TRB S_HG 3
04 3949 ALLIUK CRO S_HG 3
05 4000 Ahold CUS M_KG 3
06 4000 Ahold CUS M_KG 3
07 4000 Ahold CUS M_KG 3
08 4000 Ahold BNP S_HG 3
09 4000 Ahold TRE S_HG 3


Return all records who has the following fields duplicated Account, Client DealerNam MSEG and Tier
ID Account Client DealerNam MSEG Tier
01 3949 ALLIUK ABN S_HG 3
02 3949 ALLIUK ABN S_HG 3

05 4000 Ahold CUS M_KG 3
06 4000 Ahold CUS M_KG 3
07 4000 Ahold CUS M_KG 3

Any input will be appreciated

 

[SQLDenis]

replace table with your table name


select ID Account Client DealerNam MSEG Tier
from table t1 join(
select Account, Client, DealerNam , MSEG, Tier
from table
group by ID, Account, Client, DealerNam , MSEG, Tier
having count(*) >1) t2 on t1Account = t2.t1Account
and t1.Client = t2.Client
and t1.DealerNam = t2.DealerNam
and t1.MSEG = t2.MSEG
and t1.Tier = t2.Tier

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[vongrunt]

select A.*
from  blah A
inner join 
(	select Account, Client, DealerNam, MSEG, Tier
	from  blah B
	group by Account, Client, DealerNam, MSEG, Tier
	having count(*) > 1
) B on A.Account = B.Account and A.Client = B.Client and A.DealerNam = B.DealerNam 
	and A.MSEG=B.MSEG and A.Tier=B.Tier
-- order by A.id

------
"There's a man... He's bald and wears a short-sleeved shirt, and somehow he's very important to me. I think his name is Homer."
(Jack O'Neill, Stargate)

 

[SQLDenis]

Raul2005,
change the top select to t1.* in the query I gave you since it will complain about ambiguous columns names

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[carlen]

select count(*) AS CN,Account, Client, DealerNam, MSEG, Tier INTO #T FROM TABLE
GROUP BY Account, Client, DealerNam, MSEG, Tier

DELETE FROM #T WHERE CN < 2

SELECT * FROM TABLE T LEFT JOIN #T ON T.Account = #T.Account AND T.Client = #T.Client AND T.DealerNam = #T.DealerNam AND T.MSEG = #T.MSEG AND T.Tier = #T.Tier

WHERE T.Account IS NOT NULL AND
T.ClientIS NOT NULL AND
T.DealerNamI S NOT NULL AND
T.MSEGIS NOT NULL AND
T.TierIS NOT NULL
ORDER BY T.ID

 

[Raul2005]

Thanks

Raul