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

finding duplicates

Author

Topic

iradev
Starting Member

45 Posts

Posted - 2010-08-10 : 10:40:35

The code below finds duplicate clients records (the same regno and same code). The problem is I want to include the primary key as well (Clients.ClientId) to the results but I have no idea how. As soon as I add Clients.ClientId to the Group By clause, the query returns no results... any help ?

SELECT     
Clients.RegNo
,Locations.Code
,COUNT(*) as Occurances

FROM Clients INNER JOIN Locations ON Locations.LocationId = Clients.LocationId

GROUP BY   
Clients.RegNo
,Locations.Code

HAVING COUNT(*) > 1

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-10 : 10:50:49

select t1.clientid,t2.* from clients as t1 inner join
(
SELECT
Clients.RegNo
,Locations.Code
,COUNT(*) as Occurances

FROM Clients INNER JOIN Locations ON Locations.LocationId = Clients.LocationId

GROUP BY
Clients.RegNo
,Locations.Code

HAVING COUNT(*) > 1
) as t2
on t1.RegNo=t2.RegNo

Madhivanan

Failing to plan is Planning to fail

iradev
Starting Member

45 Posts

Posted - 2010-08-12 : 06:36:03

I just realised I am getting multiple postcodes per single ClientId. A ClientId could only have a single postcode and regno. Any ideas?

quote:
Originally posted by madhivanan

select t1.clientid,t2.* from clients as t1 inner join
(
SELECT
Clients.RegNo
,Locations.Code
,COUNT(*) as Occurances

FROM Clients INNER JOIN Locations ON Locations.LocationId = Clients.LocationId

GROUP BY
Clients.RegNo
,Locations.Code

HAVING COUNT(*) > 1
) as t2
on t1.RegNo=t2.RegNo

Madhivanan

Failing to plan is Planning to fail

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2010-08-12 : 16:01:13

either you should normalize your data or create new constraints against the table to prevent duplicate insertions -> also those that make no sense; e.g.: client having two postal codes at one addresslocation.

send DDL and some sample data.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources