How to combine two sql queries to get a one table in mysql?-CodePudding

I have a dataset as follows,

   Time         Number  Updated
2/10/2022 10:12 12345     0    
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     1

I need to get Time,Number and Last updated time if updated value is 1 for each number. The output is should be as this.

Time            Number  Updated    Last Updated
2/10/2022 10:12 12345     0      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     1      2/15/2022 10:12

I know I need to get following two query data, but I couldn't combine them to get a one query.

select Time,Number from table1;

select Number, max(Time) from table1 group by Number ;

From each one I can get the output as follows,

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


    Number  max(Time)
12345   2/15/2022 10:12
234     2/11/2022 10:12
433     2/12/2022 10:12
556     2/13/2022 10:12
4357    2/14/2022 10:12
12345   2/15/2022 10:12

can some one show me an efficient way to do this?Since I have thousands of queries to select as this.

Update:

How I could get the required outputs when the database changed 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

and the results should be as follows,

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

CodePudding user response：

We can use MAX() here as an analytic function:

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

The logic above will grab, for each record, the max time value as the last updated time. For numbers having only one record, the record's time will be coincident with the max time.