select mf.*
from message_flow_node mf
join
( select
FLOW_NODE_PK,
max(mfn.RECEIVE_TIME) rt
from message_flow_node mfn
join flow_node fn
on fn.pk = mfn.FLOW_NODE_PK
where fn.PARENT_CODE = #node# and
mfn.INSTANCE_ID=(select pi.INSTANCE_ID from programme_instance pi where pi.pk = #programmaPk#)
group by FLOW_NODE_PK) info
on info.FLOW_NODE_PK = mf.FLOW_NODE_PK
where mf.INSTANCE_ID=(select pi.INSTANCE_ID from programme_instance pi where pi.pk = #programmaPk#)
and mf.RECEIVE_TIME = info.rt
from message_flow_node mf
join
( select
FLOW_NODE_PK,
max(mfn.RECEIVE_TIME) rt
from message_flow_node mfn
join flow_node fn
on fn.pk = mfn.FLOW_NODE_PK
where fn.PARENT_CODE = #node# and
mfn.INSTANCE_ID=(select pi.INSTANCE_ID from programme_instance pi where pi.pk = #programmaPk#)
group by FLOW_NODE_PK) info
on info.FLOW_NODE_PK = mf.FLOW_NODE_PK
where mf.INSTANCE_ID=(select pi.INSTANCE_ID from programme_instance pi where pi.pk = #programmaPk#)
and mf.RECEIVE_TIME = info.rt