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)
 fetching duplicates

Author  Topic 

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-10-13 : 22:19:50
Hi friends

i have a table with 5 columns .there are 3 fields which should not be duplicated. now how can i write query to get those duplicates.
my table:
col1 col2 col3 col4 col5
karl 2 12/02/05 heck 12
karl 2 12/02/05 heck 12
karl 2 13/02/05 heck 12

here i want 1 and 2 records as col1,col2 and col3 are duplicated.
Any ideas.Thanks for your help.

Cheers

nathans
Aged Yak Warrior

938 Posts

Posted - 2005-10-13 : 23:41:17
What is the PK of this table?

Basic idea: Use a group by and having clause.

declare @table table (col1 varchar(10), col2 int, col3 datetime, col4 varchar(10), col5 int)
insert into @table (col1, col2, col3, col4, col5)
select 'karl', 2, '2/12/2005', 'heck', 12 union all
select 'karl', 2, '2/12/2005', 'heck', 12 union all
select 'karl', 2, '2/13/2005', 'heck', 12

select col1,
col2,
col3
from @table
group by col1, col2, col3
having count(*) > 1


Nathan Skerl
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-10-13 : 23:47:11
Thank you very much Nathan.That works beautifully :)
PK field is another field which i ignored here,sorry.
Cheers
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-10-13 : 23:49:56
BTW Nathan
if i want PK also how would i do that ??
will it be a sub query ?

Cheers
Go to Top of Page

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2005-10-14 : 00:37:13
What to do you mean by "get the duplicates"? Delete them from the table? Omit them from a query result set?
Go to Top of Page

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-14 : 01:40:09
>>if i want PK also how would i do that ??

If you have PK, then logically there is no duplicate rows


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

anuj164
Starting Member

49 Posts

Posted - 2005-10-14 : 14:32:55
yes you can use PK in that, it looks like you have logical PK.
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2005-10-16 : 15:43:02
Hi all
There is PK field which will be unique but those 3 fields may be duplicated.but as per our business rule we should not have more than 1 record with the same values in those 3 fields.
Yes,i agree there is a bug in the program but as i temporary fix we display these duplicates to user and user decides which one to keep.

Cheers
Go to Top of Page
   

- Advertisement -