How to get lag value without using the lag function in MySQL

Asked Feb 02 '18 at 22:48

Active Feb 02 '18 at 23:11

Viewed 528 times

If I have

id   minutes
1     2:40:00 
1     3:40:00
1     5:40:00
2     6:40:00
2     7:40:00
2     8:40:00

With the lag function

SELECT id, minutes as earlier_time, lag(minutes) OVER(PARTITION BY id ORDER BY id, minutes) as later_time FROM table1

I get

   id   earlier_time 
    1     2:40:00    3:40:00
    1     3:40:00    5:40:00
    1     5:40:00    NULL
    2     6:40:00    7:40:00
    2     7:40:00    8:40:00
    2     8:40:00    NULL

Is there a way to do that without the lag function?

edited Feb 02 '18 at 23:07

asked Feb 02 '18 at 22:48

rose

MySQL doesn't have a `LAG` function until MySQL 8.0. – Barmar Feb 02 '18 at 22:55
1

yeah thats's why I need a way to do that without the LAG function... @Barmar – rose Feb 02 '18 at 22:56
@Barmar That question is different. It doesn't return the previous value. – rose Feb 02 '18 at 23:08
I've added several more. All you have to do is search SO for `[mysql] lag function` – Barmar Feb 02 '18 at 23:11
Got it. Thanks! @Barmar – rose Feb 02 '18 at 23:21
@rose Concider to solve that issue in application language. 3 of the 4 linked answers are using an unreliable "hack". – Paul Spiegel Feb 02 '18 at 23:40

How to get lag value without using the lag function in MySQL

0 Answers0