| Author |
 Topic  |
|
|
ultraman
Starting Member
18 Posts |
 Posted - 11/11/2004 : 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
USA
7423 Posts |
Posted - 11/11/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/11/2004 : 14:54:16
|
Select col1, col2, count(*) from yourTable Group By col1, col2 Having count(*)>1
Corey
 |
|
|
|
ultraman
Starting Member
18 Posts |
Posted - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 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 - 11/12/2004 : 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 - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 11:36:35
|
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 |
|
|
|
ultraman
Starting Member
18 Posts |
Posted - 11/12/2004 : 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
Flowing Fount of Yak Knowledge
USA
2878 Posts |
Posted - 11/12/2004 : 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
|
|
|
| |
Topic |
|
Find doubles on a 2 field table
