Search code examples
mysqlcalculated-columnscalculated-field

MySQL: Select and calculate values from next row

I have a little trouble here. If i have the following table:

ID  | Name | ini_day
-----------------------
1   | Juan | 1
2   | Nora | 5
3   | Pepe | 9
4   | Lara | 12
5   | Alex | 18
6   | Lily | 20

I need to calculate the last day of work based in the next ini_day minus 1. Expected result:

ID  | Name | ini_day | end_day
------------------------------
1   | Juan | 1       | 4
2   | Nora | 5       | 8
3   | Pepe | 9       | 11
4   | Lara | 12      | 17
5   | Alex | 18      | 19
6   | Lily | 20      | NULL

How i achieve it? Thanks in advance!

Solution

  • On MySQL 8+, we can use the LEAD() window function:

    SELECT ID, Name, ini_day, LEAD(ini_day) OVER (ORDER BY ID) - 1 AS end_day
FROM yourTable
ORDER BY ID;