By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE t
(ENO varchar2(4), Sequence int, Status varchar2(3))
;
INSERT ALL
INTO t (ENO, Sequence, Status)
VALUES ('A101', 1, 'New')
INTO t (ENO, Sequence, Status)
VALUES ('A101', 2, 'Old')
INTO t (ENO, Sequence, Status)
VALUES ('A101', 3, 'Old')
INTO t (ENO, Sequence, Status)
VALUES ('A101', 4, 'Old')
INTO t (ENO, Sequence, Status)
VALUES ('B111', 25, 'New')
INTO t (ENO, Sequence, Status)
VALUES ('B111', 3, 'Old')
INTO t (ENO, Sequence, Status)
VALUES ('B111', 5, 'New')
INTO t (ENO, Sequence, Status)
VALUES ('C456', 9, 'New')
INTO t (ENO, Sequence, Status)
VALUES ('C456', 4, 'New')
INTO t (ENO, Sequence, Status)
VALUES ('C456', 6, 'New')
SELECT * FROM dual
;
10 rows affected
select *
from t
| ENO | SEQUENCE | STATUS |
|---|---|---|
| A101 | 1 | New |
| A101 | 2 | Old |
| A101 | 3 | Old |
| A101 | 4 | Old |
| B111 | 25 | New |
| B111 | 3 | Old |
| B111 | 5 | New |
| C456 | 9 | New |
| C456 | 4 | New |
| C456 | 6 | New |
select ENO
,Sequence
,Status
from (
select t.*
,count(distinct status) over(partition by eno) as cnt
from t
) t
where cnt = 2
| ENO | SEQUENCE | STATUS |
|---|---|---|
| A101 | 1 | New |
| A101 | 2 | Old |
| A101 | 3 | Old |
| A101 | 4 | Old |
| B111 | 25 | New |
| B111 | 5 | New |
| B111 | 3 | Old |