0

I have SQL code below. If I removed , LAG(myNum, 1) OVER(ORDER BY id) from the last SQL statement, my code would work. If I keep using LAG() and OVER() in the last SQL statement, I got error as 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 id) from db001.myTable001' at line 1. Any ideas how to fix? Thanks!

DROP TABLE IF EXISTS db001.myTable001;

CREATE TABLE IF NOT EXISTS db001.myTable001
    (id int, myDate VARCHAR(3), myNum int);

INSERT INTO db001.myTable001
    (id, myDate, myNum)
VALUES
    (1, 'Mon', 4),
    (2, 'Tue', 6),
    (3, 'Wed', 3);

SELECT * from db001.myTable001;

SELECT id, myDate, myNum as curNum, LAG(myNum, 1) OVER(ORDER BY id)
from db001.myTable001;
David Brossard
  • 13,584
  • 6
  • 55
  • 88
H42
  • 725
  • 2
  • 9
  • 28
  • 1
    What version of MySQL are you using? You need to be running MySQL 8.0 or later to use Windowing Functions like `LAG` and `LEAD`. (There's far too many shops _still running_ MySQL 5.x... I'll bet they're also still running PHP 4 too...) – Dai Nov 30 '21 at 00:11
  • Thanks. I am using MySQL57, and now I understand why. – H42 Nov 30 '21 at 00:21

1 Answers1

2

LAG is a window function, that is supported by MySQL since version 8.

OVER is just additional syntax that comes with window functions.

SebDieBln
  • 3,303
  • 1
  • 7
  • 21