Comparing between rows in same table in Oracle SQL

Question

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

Why would this not show 1000 ACTIVE?

Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні

2016-08-22 19:29:48 +00:00
Commented Aug 22, 2016 at 19:29 — Bob Jarvis - Слава Україні
– Bob Jarvis - Слава Україні, Commented Aug 22, 2016 at 19:29

Siyual · Accepted Answer · 2016-08-22 18:36:39Z

1

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
)

answered Aug 22, 2016 at 18:36

Siyual

17k8 gold badges47 silver badges60 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Matt · Accepted Answer · 2016-08-22 18:44:04Z

1

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

edited Aug 22, 2016 at 18:44

user5683823

answered Aug 22, 2016 at 18:40

Matt

14.5k2 gold badges19 silver badges29 bronze badges

2 Comments

miculito23 Over a year ago

Thanks everyone. I got Error ORA-00997: Illegal use of LONG datatype on the query above, on SELECT DISTINCT 0.*

Matt Over a year ago

is that a Zero [0] or an o? It should be SELECT DISTINCT o.*, but you can also specify the column names instead e.g. SELECT DISTINCT o.CustomerId, o.Status

xQbert · Accepted Answer · 2016-08-22 18:57:34Z

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.

kjmerf · Accepted Answer · 2016-08-22 21:33:03Z

0

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')

edited Aug 22, 2016 at 21:33

answered Aug 22, 2016 at 18:51

kjmerf

4,3853 gold badges24 silver badges29 bronze badges

8 Comments

miculito23 Over a year ago

Ok, ran into an issue. I'm getting hits when all the STATUS are Not Active. Which make sense base on my original request. What can be done to add code to only show when the STATUS are only all Active.

kjmerf Over a year ago

Ok, so what you really want is: if there are any Not Active, then don't return the ID at all? Right?

kjmerf Over a year ago

@miculito23 have a look at my addition above

miculito23 Over a year ago

Thank always for all the help. The above won't work. I'm looking for the results to only show when all the STATUS are only Active. Below is another example of the output I'm looking for.

miculito23 Over a year ago

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

|

Rich Benner · Accepted Answer · 2016-08-23 15:21:26Z

0

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.

answered Aug 23, 2016 at 15:21

Rich Benner

8,1419 gold badges35 silver badges39 bronze badges

Collectives™ on Stack Overflow

Comparing between rows in same table in Oracle SQL

5 Answers 5

Comments

2 Comments

Comments

8 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

Comments

8 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related