Analytic function LAG for other group

Question

I have a query that goes like this.

select m.*, lag (kod) over (partition by drv order by rn) from m;

The DRV column has only 2 possible values. The query obviously returns the "lagged" value from the same drv-group as the current row.

However, I need to select the value of the KOD column which has maximum possible RN smaller than the RN of the current row and the DRV different than the DRV of the current row. Obviously I can write a correlated subquery, but I am looking for something more efficient.

Any advice is much appreciated.

EDIT

As mentioned in the comments, some sample data will make the question clearer.

RN      DRV  KOD    LAG(KOD)
1365    lf   115892 115786
1366    zon  1159   1158
1367    lf   115927 115892
1368    zon  116    1159
1369    zon  1160   116
1370    lf   116029 115927
1371    lf   116043 116029

This is the result of the query given above. I am only interested in the records where DRV='lf'. For example for RN=1367 we have LAG(KOD)=115892 because this is the previous record in the group. Instead of taking the previous records from the same partition/group, I need a query which will return the previous record from the other group - in the case of RN=1367 it must return 1159. This is because for the record in question DRV='lf' so I want to look up KOD in the other partition which is DRV='zon' and pick LAG over order by RN, and this is the record with RN=1366. So, the rule must look like:

RN      DRV  KOD    NEW_LAG(KOD)
1365    lf  115892  ?
1366    zon 1159    ?
1367    lf  115927  1159
1368    zon 116     ?
1369    zon 1160    ?
1370    lf  116029  1160
1371    lf  116043  1160

Note that I am not interested in the results from records where DRV='zon' that's why I have put a question mark there.

Please edit your question and add some sample data and the expected output based on that (formatted text please, no screenshots) — a_horse_with_no_name
I completely agree, if you post sample data and your correlated subquery, I guess it will take 5 - 10 minutes to see analytic query:). Your current formulation is a bit vague. — Marmite Bomber
May be you make the update in a wrong time zone, it took 45 minutes:) You're welcome! — Marmite Bomber

Marmite Bomber Marmite Bomber · Accepted Answer · 2016-01-15T09:27:12

The LAST_VALUE IGNORE NULL do the job for you

with dat as (
select 1365 RN ,   'lf' DRV, 115892 KOD  from dual union all  
select 1366 RN ,   'zon' DRV, 1159 KOD  from dual union all 
select 1367 RN ,   'lf' DRV, 115927 KOD  from dual union all 
select 1368 RN ,   'zon' DRV,116 KOD  from dual union all 
select 1369 RN ,   'zon' DRV, 1160 KOD  from dual union all 
select 1370 RN ,   'lf' DRV, 116029 KOD  from dual union all 
select 1371 RN ,   'lf' DRV, 116043 KOD  from dual),
dat2 as (
select 
  RN, DRV, KOD, 
  LAST_VALUE(case when DRV = 'zon' then  KOD end IGNORE NULLS) over ( order by RN) as LAG_KOD_ZON,
  lag(KOD) over (order by RN) as LAG_KOD 
from dat
)
select  
 RN, DRV, KOD,
 CASE WHEN DRV = 'lf' THEN LAG_KOD_ZON end as LAG_KOD
from dat2;

results in

       RN DRV        KOD    LAG_KOD
---------- --- ---------- ----------
      1365 lf      115892            
      1366 zon       1159            
      1367 lf      115927       1159 
      1368 zon        116            
      1369 zon       1160            
      1370 lf      116029       1160 
      1371 lf      116043       1160

Analytic function LAG for other group

1 Answers