-6
SELECT 
    user_id, 
    unix_timestamp, 
    LAG(unix_timestamp,1) OVER (PARTITION BY user_id ORDER BY unix_timestamp) 
    As Previous_time
FROM mydb.query_one
LIMIT 5;

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 '(PARTITION BY user_id ORDER BY unix_timestamp) AS previous_time, RO' at line 5
0.056 sec

Martin
  • 22,212
  • 11
  • 70
  • 132
I. Ara
  • 47
  • 1
  • 4
  • 2
    what is the mysql version? – Sergio Tulentsev May 15 '18 at 15:21
  • I don't think this is MySQL Syntax (OVER statement does not exist in MySQL, only sql-server, postgre...) similar question : https://stackoverflow.com/questions/6292679/mysql-using-correct-syntax-for-the-over-clause Edit : you need MySQL 8 or above – Nerevar May 15 '18 at 15:35
  • 1
    Have you checked whether your MySQL support `OVER` or not???? That's probably the first thing you should check before posting here. – Eric May 15 '18 at 15:43

1 Answers1

0

MySQL only supports window functions starting with version 8.0. Instead, you can do:

SELECT qo.user_id, qo.unix_timestamp, 
       (SELECT MAX(qo2.unix_timestamp) 
        FROM mydb.query_one qo2
        WHERE qo2.user_id = qo.user_id AND qo2.unix_timestamp < qo.unix_timestamp
       ) as Previous_time
FROM mydb.query_one
LIMIT 5;

For performance you want an index on (user_id, unix_timestamp).

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786