rat out 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

SQL Server 2000 Forums

Transact-SQL (2000)

rat out duplicates

Author

Topic

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-21 : 14:25:18

Really lame question.

declare @a table (id int, cde char(1))
insert	@a
select	1, 'A' union all
select	2, 'D' union all
select	2, 'A' union all
select	3, 'Z'

I want to list id and cde for these ids that have more than one record. In this case:

2, 'D'
2, 'A'

So I tried:

select @a.id, @a.cde
from @a
inner join (
	select id, count(*) as cnt
	from @a group by id
     ) as c
on @a.id=c.id
where c.cnt > 1

But no joy. What am I doing wrong?

TIA

ch9862
Yak Posting Veteran

76 Posts

Posted - 2007-02-21 : 14:36:44

Scratch that - I got it =).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources