CODEWITHSUNDEEP
oracle-databaselead
user2790376
user2790376

Reputation: 13

Oracle Lead by group

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 ?

Upvotes: 0

Views: 2225

Answers (3)

A1lan4
A1lan4

Reputation: 1

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.

Upvotes: 0

Boneist
Boneist

Reputation: 23588

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

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59602

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;

Upvotes: 0

Related Questions