Identifying duplicates on an cli_id1

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

Identifying duplicates on an cli_id1

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-09-26 : 08:23:57

I have this query below that I created to do a count, but I don't think this is what I needed.

I need to find the duplicates. Example, if

CLI_ID1 12345 has 4 CLIP records, each CLIP record should have a different CLIP rank. I need to find scenarios where 2 (or more) of the CLIP records have the same CLIP RANK. If there are duplicate CLIP_RANKs within the same CLI_ID,

Select Distinct

cli_id1, count(clip_rank) countrank

FROM impact.dbo.CLI
LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1

left join
impact.dbo.clip ON cli_id1 = clip_id1
Where (clio_trm = '' or clio_trm = NULL or clio_trm is null)
group by cli_id1
order by cli_id1

mmkrishna1919
Yak Posting Veteran

95 Posts

Posted - 2014-09-26 : 09:46:04

Select Distinct cli_id1, count(clip_rank) countrank
FROM impact.dbo.CLI
LEFT JOIN impact.dbo.CLIO ON CLI.CLI_ID1 = CLIO.clio_id1
left join impact.dbo.clip ON cli_id1 = clip_id1
Where (clio_trm = '' or clio_trm = NULL or clio_trm is null)
group by cli_id1
having count(clip_rank) - count(distinct clip_rank) > 1
order by cli_id1

M.MURALI kRISHNA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources