Find doubles on a 2 field table

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)

Find doubles on a 2 field table

Author

Topic

ultraman
Starting Member

18 Posts

Posted - 2004-11-11 : 13:21:57

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

7423 Posts

Posted - 2004-11-11 : 13:30:04

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-11 : 14:54:16

Select col1, col2, count(*) from yourTable Group By col1, col2 Having count(*)>1

Corey

ultraman
Starting Member

18 Posts

Posted - 2004-11-12 : 10:06:48

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 10:09:32

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

ultraman
Starting Member

18 Posts

Posted - 2004-11-12 : 10:24:44

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

ultraman
Starting Member

18 Posts

Posted - 2004-11-12 : 10:44:03

Another question would be : how do I get only the records that have the same 2 first col but different col3 ?

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 11:36:35

[code]
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
[/code]

Corey

ultraman
Starting Member

18 Posts

Posted - 2004-11-12 : 11:54:06

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

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-11-12 : 13:57:41

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources