-2

I have a table like this one:

enter image description here

And I need to add a column with incremental value from previous value based on Data order like this:

enter image description here

How can I achieve this task ?

Luca Folin
  • 101
  • 1
  • 8
  • Find [LAG](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lag) and/or [LEAD](https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html#function_lead) in the manual, and use it. – Luuk Jul 18 '21 at 16:43

1 Answers1

0

You can make use of the function LAG or LEAD.

A simple example on a table which only has integers values from 0 to 9:

select 
   i, 
   lead(i) over (order by i) as "LEAD", 
   lag(i) over (order by i) as "LAG" 
from numm 
order by i;

will output this:

+---+------+------+
| i | LEAD | LAG  |
+---+------+------+
| 0 |    1 | NULL |
| 1 |    2 |    0 |
| 2 |    3 |    1 |
| 3 |    4 |    2 |
| 4 |    5 |    3 |
| 5 |    6 |    4 |
| 6 |    7 |    5 |
| 7 |    8 |    6 |
| 8 |    9 |    7 |
| 9 | NULL |    8 |
+---+------+------+
Luuk
  • 12,245
  • 5
  • 22
  • 33
  • ok thk I'm near the solution but I need something like this where I make the difference between lag value and the actual value of the row. What is it the correct code to do it ? : 'select i, lag(i) -i over (order by i) as "LAG" from numm order by i;' – Luca Folin Jul 18 '21 at 16:57
  • The `over (order by ....)` is part of the expression, so you need to do `lag(i) over order by i) - i` (but you could have tested this yourself within 1 minute... – Luuk Jul 18 '21 at 17:15