SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 fetching duplicates		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/13/2005 :  22:19:50  Show Profile  Reply with Quote
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

USA
933 Posts
Posted - 10/13/2005 :  23:41:17  Show Profile  Reply with Quote
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
Edited by - nathans on 10/13/2005 23:42:20
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/13/2005 :  23:47:11  Show Profile  Reply with Quote
Thank you very much Nathan.That works beautifully :)
PK field is another field which i ignored here,sorry.
Cheers
Edited by - rajani on 10/13/2005 23:48:24
Go to Top of Page

rajani
Constraint Violating Yak Guru

367 Posts
Posted - 10/13/2005 :  23:49:56  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2365 Posts
Posted - 10/14/2005 :  00:37:13  Show Profile  Reply with Quote
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

India
22469 Posts
Posted - 10/14/2005 :  01:40:09  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
>>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

USA
49 Posts
Posted - 10/14/2005 :  14:32:55  Show Profile  Send anuj164 a Yahoo! Message  Reply with Quote
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 - 10/16/2005 :  15:43:02  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000