Over Clause getting syntax errors in sql

user_17452930 0
Viewed 36

As got the answer from How to combine two sql queries to get a one table in mysql? here I have a database as follows and I need the output as below.

Time         Number  Updated
2/10/2022 10:12 12345     1    
2/11/2022 10:12 234       0    
2/12/2022 10:12 433       0    
2/13/2022 10:12 556       0    
2/14/2022 10:12 4357      0    
2/15/2022 10:12 12345     0

output:

Time            Number  Updated    Last Updated
2/10/2022 10:12 12345     1      2/15/2022 10:12
2/11/2022 10:12 234       0      2/11/2022 10:12
2/12/2022 10:12 433       0      2/12/2022 10:12
2/13/2022 10:12 556       0      2/13/2022 10:12
2/14/2022 10:12 4357      0      2/14/2022 10:12
2/15/2022 10:12 12345     0      2/15/2022 10:12

We can get the output by using following query,

SELECT
    Time,
    Number,
    MAX(Time) OVER (PARTITION BY Number) AS LastUpdated
FROM yourTable
ORDER BY Time;

This works fine when I have used it in my localhost with php file. But when I am using this same query in my remote server I am getting syntax error as,

ERROR 1064 (42000): 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....

Seems my mysql version in remote server(mysql Ver 14.14 Distrib 5.1.73) doesn't support that. Can someone provide alternative way to do this same?

mysql sql
1 Answers

If your MySQL version didn't support window function, You can try to use subquery with MAX to make it.

Query #1

SELECT
    Time,
    Number,
    (SELECT MAX(Time) FROM yourTable tt WHERE t.Number = tt.Number) LastUpdated
FROM yourTable t
ORDER BY Time;

or use ORDER BY with LIMIT to get the lastest each Number

SELECT
    Time,
    Number,
    (SELECT Time FROM yourTable tt WHERE t.Number = tt.Number ORDER BY Time DESC LIMIT 1) LastUpdated
FROM yourTable t
ORDER BY Time;
Time Number LastUpdated
2022-02-10 10:12:00 12345 2022-02-15 10:12:00
2022-02-11 10:12:00 234 2022-02-11 10:12:00
2022-02-12 10:12:00 433 2022-02-12 10:12:00
2022-02-13 10:12:00 556 2022-02-13 10:12:00
2022-02-14 10:12:00 4357 2022-02-14 10:12:00
2022-02-15 10:12:00 12345 2022-02-15 10:12:00
user_51760710
Related