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?

CodePudding user response：

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;