Lag() function not working in mysql workbench 8.0

Question

SELECT created_at, grand_total, 
lag(grand_total,1) over (order by o.grand_total desc) as 'lag'
FROM sales_flat_order;

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(order by o.grand_total desc) as 'lag' FROM sales_flat_order' at line 2 0.016 sec

code fails at: over **(**order by o.grand_total desc)

Answer 1

Check which MySQL version are you using

Simulate lag function in MySQL

MySql using correct syntax for the over clause

Answer 2

You can use a correlated subquery:

SELECT created_at, grand_total, 
       (select grand_total
        from sales_flat_order sfo2
        where sfo2.grand_total > sfo.grand_total
        order by sfo2.grand_total asc
        limit 1
       )
FROM sales_flat_order sfo;

Note that lag() with a descending sort is more commonly called "lead".