How can I find the record with id where the previous status Oracle SQL-CodePudding

please help me

How can I find this records with id and status, e.g. "d", but only one that has passed through the previous status in the past, e.g. "b", but not other way

CodePudding user response：

From Oracle 12, you can use MATCH_RECOGNIZE to perform row-by-row comparisons:

SELECT id, status, timestamp
FROM   table_name
MATCH_RECOGNIZE(
  PARTITION BY id
  ORDER     BY timestamp
  ALL ROWS PER MATCH
  PATTERN ( {- b_status any_row*? -} d_status )
  DEFINE
    b_status AS status = 'b',
    d_status AS status = 'd'
)

You can also, in earlier versions, use analytic functions:

SELECT id, status, timestamp
FROM   (
  SELECT t.*,
         COUNT(CASE WHEN status = 'b' THEN 1 END)
           OVER (PARTITION BY id ORDER BY timestamp) AS has_b
  FROM   table_name t
)
WHERE  status = 'd'
AND    has_b > 0;

Which, for the sample data:

CREATE TABLE table_name (id, status, timestamp) AS
SELECT 100, 'a', DATE '2022-02-01' FROM DUAL UNION ALL
SELECT 100, 'b', DATE '2022-02-02' FROM DUAL UNION ALL
SELECT 100, 'c', DATE '2022-02-03' FROM DUAL UNION ALL
SELECT 100, 'd', DATE '2022-02-04' FROM DUAL UNION ALL
SELECT 200, 'g', DATE '2022-02-05' FROM DUAL UNION ALL
SELECT 200, 's', DATE '2022-02-06' FROM DUAL UNION ALL
SELECT 200, 'd', DATE '2022-02-07' FROM DUAL UNION ALL
SELECT 200, 'a', DATE '2022-02-08' FROM DUAL;

Both output:

ID STATUS TIMESTAMP

100 d 2022-02-04 00:00:00

ID	STATUS	TIMESTAMP
100	d	2022-02-04 00:00:00

db<>fiddle here

CodePudding user response：

You could also use this solution using EXISTS clause.

select t1.ID, t1.status, t1.timestamp
from Your_Table t1
where t1.status = 'd'
and exists (
    select null 
    from Your_Table t2 
    where t1.id = t2.id 
      and t1.timestamp > t2.timestamp
      and t2.status in ('b')
    )
;

demo on db<>fiddle