Duplicate Data - 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

Duplicate Data

Author

Topic

yuchenchen
Starting Member

4 Posts

Posted - 2009-09-03 : 09:17:14

Hi,

I want to identify the duplicate data in my database.

For example, my data base is like this:

ID NUMBER
A 1
A 1
A 2
A 3
A 4
B 4
B 5
B 5
B 5
B 6
C 6
C 1
C 2
C 8
C 8

I want to identify the data with red color since it identify the "Number" first and then match with the "ID". However the programming i wrote right now show the duplicate data with green color
ID NUMBER
A 1
A 1
A 2
A 3
A 4
B 4
B 5
B 5
B 5
B 6
C 6
C 1
C 2
C 8
C 8

so, how should i write in order to identify the duplicate data in NUMBER and also can stick within one "ID"?

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-03 : 09:19:36

select ID,NUMBER from your_table
group by ID,NUMBER
having count(*)>1

Madhivanan

Failing to plan is Planning to fail

yuchenchen
Starting Member

4 Posts

Posted - 2009-09-03 : 09:53:53

sorry but the result is not what i am looking for.

The programming is just like what I wrote.

but thank you for your inputs.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-09-03 : 10:01:51

select t1.* from your_table as t1 inner join
(
select ID,NUMBER from your_table
group by ID,NUMBER
having count(*)>1
) as t2 on t1.id=t2.id and t1.number=t2.number

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources