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)
 Find doubles on a 2 field table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ultraman
Starting Member

18 Posts

Posted - 11/11/2004 :  13:21:57  Show Profile  Reply with Quote
Hi !

I have a table with 3 field (temp table, no PK). It looks like this :
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087


I'd like to retreive the lines that have the same values for col1 and col2 since I'd like to copy the data in another table where those 2 fields are the PK. I know it's something with the COUNT() function and an aggregate, but I can't figure exactly how... Can someone help me ?

--------------
Ultraman

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 11/11/2004 :  13:30:04  Show Profile  Visit jsmith8858's Homepage  Reply with Quote
select col1,col2
from yourtable
group by col1,col2

do you need to include any values from col3? i.e., the MIN or the MAX or the SUM or something like that?

- Jeff
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/11/2004 :  14:54:16  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Select col1, col2, count(*) from yourTable Group By col1, col2 Having count(*)>1

Corey
Go to Top of Page

ultraman
Starting Member

18 Posts

Posted - 11/12/2004 :  10:06:48  Show Profile  Reply with Quote
Thanks guys, that was too easy.... Now I have some bigger problems. I want to copy the rows from this tables with duplicates into a table where the first 2 cols are the key. That means I have to filter duplicates

--------------
Ultraman
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  10:09:32  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
if the remaining columns are the exact same as in your example:
col1|col2|col3
001|A|.087
001|B|.032
001|C|.345
002|A|.324
002|B|.724
003|A|.088
003|C|.899
001|A|.087


Then this should work for you

Insert into yourNewTable
Select distint co1, col2, col3 from yourOldTable


Corey
Go to Top of Page

ultraman
Starting Member

18 Posts

Posted - 11/12/2004 :  10:24:44  Show Profile  Reply with Quote
Well that's my problem, from what I see the col3 can have 2 distinct values. That's an error in the business process I think. Anyway if col3 have different values, it's impossible to determine which one to take. It would be just a random pick....

Thanks again, you've been very helpful.

--------------
Ultraman
Go to Top of Page

ultraman
Starting Member

18 Posts

Posted - 11/12/2004 :  10:44:03  Show Profile  Reply with Quote
Another question would be : how do I get only the records that have the same 2 first col but different col3 ?

--------------
Ultraman
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  11:36:35  Show Profile  Visit Seventhnight's Homepage  Reply with Quote

Select col1, col2, col3
From yourTable A
Inner Join 
	(
	Select col1, col2, count(*) 
	from yourTable 
	Group By col1, col2
	Having count(*)>1
	) B
On A.col1 = B.col1
and A.col2 = B.col2
Group By A.col1, A.col2, A.col3
Having count(*)=1


Corey

Edited by - Seventhnight on 11/12/2004 11:37:00
Go to Top of Page

ultraman
Starting Member

18 Posts

Posted - 11/12/2004 :  11:54:06  Show Profile  Reply with Quote
Oh man I'm impressed ! You're very good, it worked exactly as I want ! This is a very powerful language but it can be quite complex for a begginer.

--------------
Ultraman
Go to Top of Page

Seventhnight
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 11/12/2004 :  13:57:41  Show Profile  Visit Seventhnight's Homepage  Reply with Quote
Well I thank your for the compliment

Upon rereading the last query, I think a simplier query might be more reliable.

The last query I provided could provide the wrong result with the following result set


col1	col2	col3
001	A	.087
001	A	.023
001	A	.087


the query would return only the middle row

col1	col2	col3
001	A	.023



So try the following query and see if it will work for you:

Select Distinct
	A.col1,
	A.col2,
	A.col3
From yourTable A
Inner Join yourTable B
On A.col1 = B.col1
and A.col2 = B.col2
Where A.col3 <> B.col3


Corey
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.08 seconds. Powered By: Snitz Forums 2000