Count distinct to find records with different values in a column table

Nashuha Tasbir 0
Viewed 49

I have a simple question: How can I use having Count(Distinct) in SQL (Oracle to be exact) to return only the rows where there are 2 column with same record but 1 column with different record.

Example

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA2     SA2
CA1     BA3     SA1 
CA2     BA4     SA3
CA2     BA4     SA4
CA2     BA5     SA4
CA3     BA6     SA6

In that example, i'd like to create a query that shows 1 same CA_ID and SA_ID, but has different BA_ID. The query's result should show like this

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA3     SA1 
CA2     BA4     SA4
CA2     BA5     SA4
oracle sql
2 Answers
select  CA_ID   
       ,BA_ID   
       ,SA_ID
from   (select  t.*
               ,count(distinct BA_ID)  over(partition by CA_ID, SA_ID) as dcount
        from    t
       ) t
where  dcount > 1
CA_ID BA_ID SA_ID
CA1 BA1 SA1
CA1 BA2 SA1
CA1 BA3 SA1
CA2 BA4 SA4
CA2 BA5 SA4

Fiddle

DannySlor0

We can try an aggregation approach to find the matching CA_ID and SA_ID values:

WITH cte AS (
    SELECT CA_ID, SA_ID
    FROM yourTable
    GROUP BY CA_ID, SA_ID
    HAVING MIN(BA_ID) <> MAX(BA_ID)
)

SELECT t1.*
FROM yourTable t1
WHERE EXISTS (
    SELECT 1
    FROM cte t2
    WHERE t2.CA_ID = t1.CA_ID AND
          t2.SA_ID = t1.SA_ID
);
Tim Biegeleisen0
Related