CODEWITHSUNDEEP
sqloracle-databasedistinct
Matt
Matt

Reputation: 15071

Oracle SQL distinct count without outputting columns that make it distinct

I have a table which i want to only output 1 field from so it can be part of another queries WHERE statement (WHERE SID IN (THIS NEW QUERY)).

However because of this i can only include SID in the SELECT, but this is removing that is needed to make the distinct count work.

SO SELECT * FROM Tablea gives me:

SID     deta    detb
22222   8159    3763
22222   8159    3763
44444   4739    6135
44444   4739    6135
44444   4739    6134
44444   4739    6135
55555   5937    0223
55555   5936    0223
66666   8577    9497
66666   8577    9497
66666   8577    9497
66666   8576    9496
66666   8577    9497
88888   3595    0919
88888   3595    0919
88888   3595    0919
88888   3595    0914
77777   5678    3456

Then SELECT DISTINCT SID, deta, detb FROM Tablea gives me:

SID     deta    detb
22222   8159    3763
44444   4739    6134
44444   4739    6135
55555   5936    0223
55555   5937    0223
66666   8576    9496
66666   8577    9497
88888   3595    0914
88888   3595    0919
77777   5678    3456

The data i want is this:

SID     deta    detb
44444   4739    6134
44444   4739    6135
55555   5936    0223
55555   5937    0223
66666   8576    9496
66666   8577    9497
88888   3595    0914
88888   3595    0919

Which can be done by using a count of distinct, however my final output i want is this:

SID     
44444  
55555  
66666   
88888

But i cant achieve it when only outputting 1 field.

Upvotes: 0

Views: 71

Answers (2)

Boneist
Boneist

Reputation: 23578

I would do it like this:

with sample_data (SID, deta, detb) as (select 22222, 8159, 3763 from dual union all
                                       select 22222, 8159, 3763 from dual union all
                                       select 44444, 4739, 6135 from dual union all
                                       select 44444, 4739, 6135 from dual union all
                                       select 44444, 4739, 6134 from dual union all
                                       select 44444, 4739, 6135 from dual union all
                                       select 55555, 5937, 0223 from dual union all
                                       select 55555, 5936, 0223 from dual union all
                                       select 66666, 8577, 9497 from dual union all
                                       select 66666, 8577, 9497 from dual union all
                                       select 66666, 8577, 9497 from dual union all
                                       select 66666, 8576, 9496 from dual union all
                                       select 66666, 8577, 9497 from dual union all
                                       select 88888, 3595, 0919 from dual union all
                                       select 88888, 3595, 0919 from dual union all
                                       select 88888, 3595, 0919 from dual union all
                                       select 88888, 3595, 0914 from dual union all
                                       select 77777, 5678, 3456 from dual)
--- end of mimicking your sample data
select sid
from   (select distinct sid,
                        deta,
                        detb
        from   sample_data)
group by sid
having count(*) > 1;


       SID
----------
     44444
     66666
     55555
     88888

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Use group by:

SELECT SID
FROM Tablea
GROUP BY SID
HAVING COUNT(DISTINCT deta || ':' || detab) > 1;

If you actually wanted the full rows (instead of the SID values), then use window functions:

SELECT a.*
FROM (SELECT a.*, COUNT(DISTINCT deta || ':' || detab) OVER (PARTITION BY SID) as cnt
      FROM tablea a
     ) a
WHERE cnt > 1;

Upvotes: 3

Related Questions