[Cypher Query] Event transitions detection based on time

I have a following cypher script which I wish to query the transition from event1 to event2. However, the following script cannot just return the exact transition between event1 and event2, but all the event1 happens before event2, I wish to ask if there is a way to select the largest event 1 start_time or there is another way to achieve my purpose

match (e1:Event {eventType:"event1"})<-[:takePlace]-(c:Car {carID:"car1"})-[:takePlace]->(e2:Event {eventType:"event2"}) where e1.start_time > e2.start_time return e1, e2

Thanks in advance

hi Oli,

this should be one possible solution

match (e1:Event {eventType:"event1"})<-[:takePlace]-(c:Car {carID:"car1"})-[:takePlace]->(e2:Event {eventType:"event2"}) 
	where e1.start_time < e2.start_time 
with c, max(e1.start_time) as mx_st
match (c)-[:take_Place]-> (e3:Event {eventType:"event2"})  
	where e3.start_time = mx_st 
return e3 

If you want to get the latest event before e2, you have to compare

where e1.start_time < e2.start_time 

(you had it reversed).


Hi Benoit,
Thanks for your solution. The thing is, there might be more than one transition from event1 to event2 and currently we could be able to only catch 1 transition

Inspired by your answer, I come up with a solution. I was thinking to use unwind to get the max starting comparing with each event2, before unwind, it seems working, after unwind, the result is the same with your solution which contains only one result which is not my expected

match (e1:Event {eventType:"event1"})<-[:takePlace]-(c:Car {carID:"car_id"})-[:takePlace]->(e2:Event {eventType:"event2"})
with collect(distinct e2.start_time) as e2_sts
unwind e2_sts as e2_st 
match (ex:Event {eventType:"event1"})<-[:takePlace]-(:Car {carID:"car_id"})
where ex.start_time < e2_st
with max(ex.start_time) as mx_st
with collect(mx_st) as cl_mx_st
return cl_mx_st