Oracle SQL, Retrieving value according to the next value-CodePudding

I'm pretty new to Oracle SQL. I try to extract information from a single table, where the condition for selection is relative to the next row in that table.

Example.

Say I have the following table;

ID	Count
0	10
1	20
2	50
3	130

I want to select the information in the form of an initial count, and the count difference between the next value. Like:

InitCount	Diff
10	10 (Because 20-10 = 10)
20	30 (Because 50-20 = 30)
50	80

Can it be done?

Thanks!

CodePudding user response：

You may use the LAG() function:

WITH cte AS (
    SELECT ID, InitCount, LEAD(InitCount) OVER (ORDER BY ID) - InitCount AS Diff
    FROM yourTable
)

SELECT ID, InitCount, Diff
FROM cte
WHERE Diff IS NOT NULL
ORDER BY ID;

CodePudding user response：

The lead analytic function might help here; though, I don't know where initTime value comes from ...

See lines #9 and #11.

SQL> with test (id, count) as
  2    (select 0,  10 from dual union all
  3     select 1,  20 from dual union all
  4     select 2,  50 from dual union all
  5     select 3, 130 from dual
  6    )
  7  select id,
  8         count,
  9         lead(count) over (order by id) as next_count,
 10         --
 11         lead(count) over (order by id) - count as diff
 12  from test
 13  order by id;

        ID      COUNT NEXT_COUNT       DIFF
---------- ---------- ---------- ----------
         0         10         20         10
         1         20         50         30
         2         50        130         80
         3        130

SQL>