MySQL: Select and calculate values from next row

Question

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!

what is your mysql version? Check using SELECT VERSION();

Asgar
– Asgar

2024-04-30 11:07:26 +00:00
Commented Apr 30, 2024 at 11:07 — Asgar
– Asgar, Commented Apr 30, 2024 at 11:07

Tim Biegeleisen · Accepted Answer · 2024-04-30 11:07:50Z

2

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;

answered Apr 30, 2024 at 11:07

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rubin Porwal · Accepted Answer · 2024-04-30 11:39:30Z

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`

answered Apr 30, 2024 at 11:39

Rubin Porwal

3,8451 gold badge27 silver badges27 bronze badges

Collectives™ on Stack Overflow

MySQL: Select and calculate values from next row

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related