Oracle Lead by group

Question

Hello I've got a problem with lead and retrieving the next value from the next group.

I've got this table:

TableA

-----------------
   ID    | value
-----------------
   1     |  2.5    
   1     |  1    
   1     |  4.5    
   2     |  7    
   2     |  2  
   3     |  3  
   4     |  1  
   4     |  7

Expected result:

------------------------------
   ID    | value   |   lead_id
------------------------------
   1     |  2.5    |    2
   1     |  1      |    2
   1     |  4.5    |    2
   2     |  7      |    3
   2     |  2      |    3
   3     |  3      |    4
   4     |  1      |   NULL
   4     |  7      |   NULL

My SQL:

select ID, value, lead(id) OVER (order by id) lead_id from TableA

Is it possible to get that result ?

We see your expected result. However, we don't see your actual result. What is the wrong result you're getting? — peter.hrasko.sk, Nov 04 '15 at 12:56
I reckon you are getting lead_ID-1 were you would expect to get lead_ID-2 on the first two lines, that's because lead doesn't give you the next distinct value, but the next actual value in the table. That means that in your case, the lead_id for the first two rows is 1, and only the third row has a lead value of 2. — Yaron Idan, Nov 04 '15 at 12:59

score 1 · Accepted Answer · answered Nov 04 '15 at 13:11

You can do this by adding in a windowing clause into the first_value analytic function:

with tablea as (select 1 id, 2.5 value from dual union all
                select 1 id, 1 value from dual union all
                select 1 id, 4.5 value from dual union all
                select 2 id, 7 value from dual union all
                select 2 id, 2 value from dual union all
                select 3 id, 3 value from dual union all
                select 4 id, 1 value from dual union all
                select 4 id, 7 value from dual)
select id,
       value,
       first_value(id) over (order by id
                             range between 1 following and unbounded following) lead_id
from   tablea;

        ID      VALUE    LEAD_ID
---------- ---------- ----------
         1        2.5          2
         1          1          2
         1        4.5          2
         2          7          3
         2          2          3
         3          3          4
         4          1

score 0 · Answer 2 · answered Nov 04 '15 at 13:08

0

I think this gives the right output:

WITH g AS 
    (SELECT ID, lead(ID) OVER (ORDER BY ID) lead_id 
     FROM (SELECT DISTINCT ID FROM TableA) ) 
SELECT ID, VALUE, lead_id 
FROM TableA
    JOIN g USING (ID)
ORDER BY 1;

answered Nov 04 '15 at 13:08

Wernfried Domscheit

54,457
9
76
110

score 0 · Answer 3 · answered Nov 04 '15 at 16:27

SELECT tablea.*, b.nextid FROM tablea
   INNER JOIN (SELECT id, LEAD (id) OVER (ORDER BY id) nextid
                FROM (  SELECT DISTINCT id
                          FROM tablea
                          ORDER BY id)) b
      ON tablea.id = b.id

This should work.

Oracle Lead by group

3 Answers3