LEAD () Window Function not working in MYSQL Workbench

Question

SELECT x,y,z,
LEAD(x) OVER(PARTITION BY y,z ORDER BY x) AS column
FROM Table;

X here is a timestamp field, This is not working in MYSQL Workbench. I am getting the below error ( is not valid at this position, expecting EOF,';'
What is wrong here? Am I missing something?

any workaround to achieve the same result in this version itself? — curious123, Nov 19 '20 at 16:19

score 0 · Answer 1 · answered Nov 19 '20 at 16:20

Window functions are supported in MySQL 8.0 only. In earlier versions, one workaround uses a correlated subquery:

select x, y, z,
    (select t1.x from mytable t1 where t1.y = t.y and t1.z = t.z and t1.x > t.x order by t1.x limit 1) as lead_x
from mytable t

This somehow assumes no duplicates (x, y, z) (otherwise, the result may differ - but the results of the window functions are not stable in that case anyway).

You could also use aggregation rather than limit:

select x, y, z,
    (select min(t1.x) from mytable t1 where t1.y = t.y and t1.z = t.z and t1.x > t.x ) as lead_x
from mytable t

LEAD () Window Function not working in MYSQL Workbench

1 Answers1