Duplicates values  
Author Message
jho





PostPosted: Mon Nov 17 20:27:40 CST 2003 Top

SQL Server Developer >> Duplicates values

I have a table called T1 and primary key A, columns B and
C.
I would like to determine if there exist duplicate values
for column B and C.

A B C
1 2 3
2 3 4
3 2 3

There exist duplicate values for columns B and C. (2,3)
Primary keys 1 and 3 have duplicate values.

Please help me write a script to determine duplicate
values for columns B and C.

Thank You,

Dean

SQL Server135  
 
 
Brian





PostPosted: Mon Nov 17 20:27:40 CST 2003 Top

SQL Server Developer >> Duplicates values This will show you the valules that are duped.



select
count(*) NumDupRows
,ColB
,ColC
from
T1
group by
ColB, ColC
having
count(*) > 1


--

Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com




>
> I have a table called T1 and primary key A, columns B and
> C.
> I would like to determine if there exist duplicate values
> for column B and C.
>
> A B C
> 1 2 3
> 2 3 4
> 3 2 3
>
> There exist duplicate values for columns B and C. (2,3)
> Primary keys 1 and 3 have duplicate values.
>
> Please help me write a script to determine duplicate
> values for columns B and C.
>
> Thank You,
>
> Dean


 
 
Louis





PostPosted: Mon Nov 17 20:37:30 CST 2003 Top

SQL Server Developer >> Duplicates values Sure:

create table pleasePostDDL
(
pleasePostDDLId int primary key,
B char,
C char
)
go
insert into pleasePostDDL (pleasePostDDLId, b, c)
values (1,2,3)
insert into pleasePostDDL (pleasePostDDLId, b, c)
values (2,3,4)
insert into pleasePostDDL (pleasePostDDLId, b, c)
values (3,2,3)
go

This query will give you the duplicate values:

select b, c
from pleasePostDDL
group by b,c
having count(*) > 1
go

Now assuming that you want to do more than just look at them:

select pleasePostDDL.pleasePostDDLId, pleasePostDDL.B, pleasePostDDL.C
from pleasePostDDL
join ( select b, c
from pleasePostDDL
group by b,c
having count(*) > 1) as dup
on pleasePostDDL.b = dup.b
and pleasePostDDL.C = dup.c

Answers, and sarcasm. Can you beat it :)


--
----------------------------------------------------------------------------
-----------

Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

Note: Please reply to the newsgroups only unless you are
interested in consulting services. All other replies will be ignored :)



>
> I have a table called T1 and primary key A, columns B and
> C.
> I would like to determine if there exist duplicate values
> for column B and C.
>
> A B C
> 1 2 3
> 2 3 4
> 3 2 3
>
> There exist duplicate values for columns B and C. (2,3)
> Primary keys 1 and 3 have duplicate values.
>
> Please help me write a script to determine duplicate
> values for columns B and C.
>
> Thank You,
>
> Dean