CODEWITHSUNDEEP
sqlsql-server-2008
user1625066
user1625066

Reputation:

Select records where all rows have same value in two columns

Here is my sample table

Col1   Col2
 A      1
 B      1
 A      1
 B      2
 C      3

I want to be able to select distinct records where all rows have the same value in Col1 and Col2. So my answer should be

Col1   Col2
 A      1
 C      3

I tried 

SELECT Col1, Col2 FROM Table GROUP BY Col1, Col2

This gives me 

Col1   Col2
 A       1
 B       1
 B       2
 C       3

which is not the result I am looking for. Any tips would be appreciated.

Upvotes: 3

Views: 6280

Answers (3)

Aditya Kakirde
Aditya Kakirde

Reputation: 5225

you can try either of the below -

select col1, col2 from 
(             
select 'A'    Col1 ,   1 Col2
from dual
union all 
select 'B'      , 1
from dual
union all
select 'A'       ,1
from dual
union all
select 'B'       ,2
from dual
)
group by col1, col2
having count(*) >1;

OR

select col1, col2
from
( 
    select col1, col2, row_number() over (partition by col1, col2 order by col1, col2) cnt 
    from 
        (             
        select 'A'    Col1 ,   1 Col2
        from dual
        union all 
        select 'B'      , 1
        from dual
        union all
        select 'A'       ,1
        from dual
        union all
        select 'B'       ,2
        from dual
        )
)
where cnt>1;

Upvotes: 0

Fauzi88
Fauzi88

Reputation: 713

Try this:

SELECT * FROM MYTABLE
GROUP BY Col1, Col2
HAVING COUNT(*)>1

For example SQLFiddle here

Upvotes: 1

Mosty Mostacho
Mosty Mostacho

Reputation: 43434

Try this out:

SELECT col1, MAX(col2) aCol2 FROM t
GROUP BY col1
HAVING COUNT(DISTINCT col2) = 1

Output:

| COL1 | ACOL2 |
|------|-------|
|    A |     1 |
|    C |     3 |

Fiddle here.

Basically, this makes sure that amount the different values for col2 are unique for a given col1.

Upvotes: 5

Related Questions