SELECT the newest record in Oracle based on date, activity in the list of records

Question

I have following data in one of the table

I would like to distinguished them based on the highlighted record, for example: when I select record based on Foreign_key column and the second record is "Content = Response OR Process_id = 11" then I need to label it as "PROCESS". If the second record is not Content = Response but instead "Content = Initiate OR Process_id = 20" (Foreign_key = 101) then I want to label it "NOT PROCESS".

HINT: activity in this table is created in the Processdate order (ASC).

Can anyone suggest me SQL query, bear in mind I need to execute the query inside CASE due to loads of other JOIN statements.

Answer 1

Analytic functions are your rescue

Here a complete example:

drop table test;
create table test
(id number,
content varchar2(30),
processdate date,
process_id number,
foreighn_key number);

insert into test values(1,'New',to_date('18.06.2015 10','dd.mm.yyyy hh24'), 10,100);
insert into test values(2,'Response',to_date('18.06.2015 11','dd.mm.yyyy hh24'), 11,100);
insert into test values(3,'Reply',to_date('18.06.2015 12','dd.mm.yyyy hh24'), 12,100);
insert into test values(4,'Closed',to_date('18.06.2015 13','dd.mm.yyyy hh24'), 13,100);
insert into test values(5,'New',to_date('18.06.2015 14','dd.mm.yyyy hh24'), 10,101);
insert into test values(6,'Initiate',to_date('18.06.2015 15','dd.mm.yyyy hh24'), 20,101);
insert into test values(7,'Target',to_date('18.06.2015 16','dd.mm.yyyy hh24'), 21,101);
insert into test values(8,'Closed',to_date('18.06.2015 17','dd.mm.yyyy hh24'), 13,101);
commit;

with trans as (
select ID, CONTENT, PROCESSDATE, PROCESS_ID, FOREIGHN_KEY,
-- first row per FK gets rn = 1
row_number() over (partition by FOREIGHN_KEY order by PROCESSDATE) rn,
-- lookup the next row value of the column
lead(CONTENT) over (partition by FOREIGHN_KEY order by PROCESSDATE) content_lead,
lead(PROCESS_ID) over (partition by FOREIGHN_KEY order by PROCESSDATE) Process_id_lead
from test order by processdate)
select 
 ID, CONTENT, PROCESSDATE, PROCESS_ID, FOREIGHN_KEY,
 case when content_lead = 'Response' OR Process_id_lead = 11 then 'PROCESS'
 when content_lead = 'Initiate' OR Process_id_lead = 20 then 'NOT PROCESS' end as trans_type
from trans 
where rn = 1

gives

    ID CONTENT                        PROCESSDATE         PROCESS_ID FOREIGHN_KEY TRANS_TYPE
---------- ------------------------------ ------------------- ---------- ------------ -----------
     1 New                            18.06.2015 10:00:00         10          100 PROCESS     
     5 New                            18.06.2015 14:00:00         10          101 NOT PROCESS