Mysql Question posted in
The official MySQL documentation can be found here.

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!

Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    According to documentation of MYSQL

    LEAD() Returns the value of expr from the row that leads (follows) the
    current row by N rows within its partition

    As a solution to above problem statement please try executing following SQL query.

    SELECT id,name,ini_day,LEAD(ini_day,1) OVER () -1 as end_day
FROM `table`
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search