MySQL Lead function not working. I have tried but its working as lag function. Kindly help me

Question

I am trying to implement the lead function functionality in select query from yesterday onwards but its not working for me.

SELECT inbp_ingangsdatum,
       previous
FROM   (SELECT ibp.inbp_ingangsdatum,
               @prev                      previous,
               @prev := inbp_ingangsdatum AS prev
        FROM   base ibp,
               (SELECT @prev := '') r
        ORDER  BY inbp_ingangsdatum) AS t1;

Current Output

val         previous
20090101    
20120401    20090101
20120402    20120401
20120403    20120402
20120404    20120403

Expected Output

val         previous

20090101    20120401
20120401    20120402
20120402    20120403
20120403    20120404
20120404    20120405

Does it work if you order by `DESC` inside the numbering sub query? — Salman A, Nov 22 '18 at 10:26
@NarenP in recent versions of MySQL; an explicit ordering needs to be done first in a Derived table, and then in outer subquery, session variables are utilized. Otherwise the order is not guaranteed — Madhur Bhaiya, Nov 22 '18 at 10:59
@NarenP Please see: [How to accept an answer for closure](https://meta.stackexchange.com/a/5235). You get points for it as well. Thanks :) — Madhur Bhaiya, Nov 26 '18 at 10:54

Salman A · Answer 1 · 2018-11-22T13:16:48.960

1

I would use a correlated sub query and pay extra attention to ties:

SELECT INSD_ID, inbp_ingangsdatum, (
    SELECT inbp_ingangsdatum
    FROM base AS x
    WHERE (inbp_ingangsdatum = base.inbp_ingangsdatum AND INSD_ID > base.INSD_ID)
    OR    (inbp_ingangsdatum > base.inbp_ingangsdatum)
    ORDER BY inbp_ingangsdatum
    LIMIT 1
) AS next_date
FROM base
ORDER BY inbp_ingangsdatum, INSD_ID

edited Nov 22 '18 at 13:16

answered Nov 22 '18 at 10:29

Salman A

262,204
82
430
521

Thanks.but,i got this answer. output: 20090101 20120401 20120401 20121001 20120401 20121001 20120401 20121001 20120401 20121001 20120401 20121001 – Naren P Nov 22 '18 at 10:53
Seems like you have "ties". You need a tie breaker column such as ID/primary key. Do you have one? – Salman A Nov 22 '18 at 11:01
Yes INSD_ID colums contains primary key – Naren P Nov 22 '18 at 11:06
@NarenP see revised answer. You can use it _if_ you have problems getting correct results with user variables. – Salman A Nov 22 '18 at 11:41

Madhur Bhaiya · Answer 2 · 2018-11-22T11:45:31.150

LEAD() function fetches the "next row" value. I don't know why are you calling it as prev. I am using the alias next instead for clarity.

While using user-defined variables, we can access previous row's value in SELECT clause, but getting value of the next (upcoming) row is tricky. We will have to explicitly order by inbp_ingangsdatum in Descending order (opposite to our final sorting requirement).

Now, we will use this result-set to get the "next" row. Due to reversed order, previous value is basically the next value.

We will finally reorder the result-set in outermost query.

Schema (MySQL v5.6)

Create table base (inbp_ingangsdatum int);

insert into base
values 
(20090101),
(20120401),
(20120402),
(20120403),
(20120404),
(20120405);

Query #1

SELECT 
  dt2.val, dt2.next
FROM 
(
  SELECT
    @nxt AS next, 
    @nxt := dt.inbp_ingangsdatum AS val 
  FROM 
  (
    SELECT
      ibp.inbp_ingangsdatum
    FROM base AS ibp
    ORDER BY ibp.inbp_ingangsdatum DESC 
  ) AS dt 
  CROSS JOIN (SELECT @nxt := '') AS user_init_vars
) AS dt2 
ORDER BY dt2.val;

Result:

| val      | next     |
| -------- | -------- |
| 20090101 | 20120401 |
| 20120401 | 20120402 |
| 20120402 | 20120403 |
| 20120403 | 20120404 |
| 20120404 | 20120405 |
| 20120405 |          |

View on DB Fiddle

MySQL Lead function not working. I have tried but its working as lag function. Kindly help me

2 Answers2