CODEWITHSUNDEEP
sqloracle-database
miculito23
miculito23

Reputation: 1

Comparing between rows in same table in Oracle SQL

I'm trying to find the best way to compare between rows by CustomerID and Status. In other words, only show the CustomerID when the status are equal between multiple rows and CustomerID. If not, don't show the CustomerID.

Example data

CUSTOMERID        STATUS
1000              ACTIVE
1000              ACTIVE
1000              NOT ACTIVE
2000              ACTIVE
2000              ACTIVE

RESULT I'm hoping for

CUSTOMERID        STATUS
2000              ACTIVE

Upvotes: 0

Views: 9286

Answers (5)

Rich Benner
Rich Benner

Reputation: 8113

This is a SQL Server answer, I believe it should work in Oracle. 

SELECT
    a.AGMTNUM
FROM TableA a
WHERE NOT EXISTS (SELECT 1 FROM TableB b WHERE b.Status = 'NOT ACTIVE' AND a.AGMTNUM = b.AGMTNUM)
AND EXISTS (SELECT 1 FROM TableB c WHERE c.Status = 'ACTIVE' AND a.AGMTNUM = c.AGMTNUM)

This will only return values that have at least one 'ACTIVE' value and no 'NOT ACTIVE' values.

Upvotes: 0

kjmerf
kjmerf

Reputation: 4345

Here's a pretty simple one using IN:

SELECT DISTINCT CustomerID, Status
FROM My_Table
WHERE CustomerID IN
(SELECT CustomerID
FROM My_Table
GROUP BY CustomerID
HAVING COUNT(Distinct Status) = 1)

Addition: based on your comment, it seems what you really want is all the IDs that do not have a 'Not Active' row, which is actually easier:

SELECT Distinct CustomerID, Status
FROM My_Table
WHERE CustomerID NOT IN
(SELECT CustomerID
 FROM My_Table
 WHERE Status = 'Not Active')

Upvotes: 0

xQbert
xQbert

Reputation: 35343

The only "Code" here is the last 4 lines in the code block. The other is establishing sample data.

with T1 as (
Select 1000 as CUSTOMERID,              'ACTIVE' as STATUS from dual union all 
select 1000,              'ACTIVE' from dual union all 
select 1000,              'NOT ACTIVE' from dual union all 
select 2000,              'ACTIVE' from dual union all 
select 2000,              'ACTIVE' from dual )

SELECT customerID, max(status) as status
FROM T1
GROUP BY  customerID
HAVING count(distinct Status) = 1
  • I used a CTE to setup sample data and called this Common table Expression T1.
  • Order of operations matter here. First the table T1 is identified
  • second the engine groups by customer ID
  • third the engine limits the results to those records having a distinct record status matching 1 and only 1 value.
  • 4th the engine picks the max status which will always be 1 value. min/max it doesn't matter as there is only 1 possible value. note, we have to use an aggregate here since we can't group by status or you wouldn't get the desired results.

Upvotes: 1

Matt
Matt

Reputation: 14381

SELECT DISTINCT o.*
FROM
    (
       SELECT
          CustomerId
       FROm
          TableName 
       GROUP BY
          CustomerId
       HAVING
          COUNT(DISTINCT Status) = 1
    ) t
    INNER JOIN TableName o
    ON t.CustomerId = o.CustomerId

Upvotes: 1

Siyual
Siyual

Reputation: 16917

You can do this with a WHERE NOT EXISTS:

Select  Distinct CustomerId, Status
From    YourTable   A
Where Not Exists
(
    Select  *
    From    YourTable   B
    Where   A.CustomerId = B.CustomerId
    And     A.Status <> B.Status
)

Upvotes: 1

Related Questions