×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

LAG error

LAG error

LAG error

(OP)
I am trying to run a simple SELECT command that includes LAG on goormIDE and keep getting an error message. I have a table with 2 columns: date, amount, and I am trying to retrieve a list of the amounts and next to each amount the amount from the previous row as opening_balance. Here's the code I use:
SELECT date, amount, LAG(amount, 1) OVER () opening_balance FROM transactions;
The error message I get is:
"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 '() opening_balance FROM transactions' at line 1".
Can anyone help a newbee with this?

RE: LAG error

Hi

Works for me :

CODE --> terminal

test> select * from transactions;
+------------+--------+
| date       | amount |
+------------+--------+
| 2019-12-01 |      1 |
| 2019-12-01 |    1.1 |
| 2019-12-01 |    1.2 |
| 2019-12-02 |      2 |
| 2019-12-03 |    3.3 |
| 2019-12-03 |   3.33 |
+------------+--------+
6 rows in set (0.00 sec)

test> SELECT date, amount, LAG(amount, 1) OVER () opening_balance FROM transactions; 
+------------+--------+-----------------+
| date       | amount | opening_balance |
+------------+--------+-----------------+
| 2019-12-01 |      1 |            NULL |
| 2019-12-01 |    1.1 |               1 |
| 2019-12-01 |    1.2 |             1.1 |
| 2019-12-02 |      2 |             1.2 |
| 2019-12-03 |    3.3 |               2 |
| 2019-12-03 |   3.33 |             3.3 |
+------------+--------+-----------------+
6 rows in set (0.00 sec)

test> select version();
+-------------------------+
| version()               |
+-------------------------+
| 8.0.18-0ubuntu0.19.10.1 |
+-------------------------+
1 row in set (0.00 sec) 

Are you sure your MySQL server supports windowing functions ? Please note that they appeared only in version 8.0.

Feherke.
feherke.github.io

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close