CODEWITHSUNDEEP
sqldb2
NuCradle
NuCradle

Reputation: 705

How to Query the Last Entry Based on Another Lookup Criteria

I have a history table that registers events on entities in an application. I am looking for a query that would give me the most recent record of an event B if only another event (A) had occurred right before it.

ENTITY_NO    EVENT    CREATE_DATE    PERSON_ID
EN10         B        2017-02-08     1234
EN10         A        2017-02-04     NULL
EN10         X        2017-01-24     NULL
EN10         Y        2017-01-22     NULL
EN10         Z        2017-01-19     NULL

For instance, in above table, I want to retrieve record EVENT B only and only if there was a record with EVENT A occurred right before the entry with EVENT B (for the same ENTITY_NO) and no other record after it.

I'm trying to run this in DB2 11.

Upvotes: 0

Views: 43

Answers (3)

Esperento57
Esperento57

Reputation: 17472

other method :

with tablewithrank as (
select f0.*, rownumber() over(partition by f0.ENTITY_NO order by f0.CREATE_DATE) rang 
from yourtable f0
)
select * from tablewithrank f1
inner join tablewithrank f2 on (f1.ENTITY_NO, f1.rang-1)=(f2.ENTITY_NO, f2.rang) and f2.EVENT='A'
left outer join tablewithrank f3 on (f1.ENTITY_NO, f1.rang+1)=(f3.ENTITY_NO, f3.rang)
where  f1.EVENT='B' and f3.ENTITY_NO is null

Upvotes: 1

Esperento57
Esperento57

Reputation: 17472

If your DB2 version dont have Lag and Lead function, you can do it :

select * from yourtable f1

inner join lateral
(
  select * from yourtable f0
  where f0.ENTITY_NO=f1.ENTITY_NO and f0.CREATE_DATE<f1.CREATE_DATE 
  order by f0.CREATE_DATE desc
  fetch first rows only       
) f2 on 1=1

left outer join lateral
(
  select * from yourtable f0
  where f0.ENTITY_NO=f1.ENTITY_NO and f0.CREATE_DATE>f1.CREATE_DATE 
  fetch first rows only       
) f3 on 1=1 

where f1.EVENT='B' and f2.EVENT='A' and f3.ENTITY_NO is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270391

You can use lead() and lag():

select t.*
from (select t.*,
             lag(event) over (partition by entity_no order by date) as prev_event,
             lead(event) over (partition by entity_no order by date) as next_event
      from t
     ) t
where event = 'B' and prev_event = 'A' and next_event is null

Upvotes: 2

Related Questions