How to get the last updated specific column values using mysql?-CodePudding

I am trying to manipulate the sql statement.

Below is the table.

CREATE TABLE MY_TABLE (
    LOG_TIME    VARCHAR(14)   NOT NULL  
  , USR_ID      VARCHAR(10)   NOT NULL      
  , INFO_1     VARCHAR(20)   NULL 
  , INFO_2     VARCHAR(20)   NULL     
  , INFO_3     VARCHAR(20)   NULL          
  , PRIMARY KEY (LOG_TIME)
)

After inserting some records as shown below,

LOG_TIME            USR_ID       INFO_1       INFO_2       INFO_3
20220305010101      USER_1       MALE         -            -
20220305022554      USER_1       -            BANGKOK      BASKETBALL
20220305052554      USER_1       -            -            BASEBALL
20220306010101      USER_2       -            SEOUL        BASEBALL
20220306022554      USER_2       -            TOKYO        -
20220307052554      USER_2       FEMALE       -            SOCCER

What I want to extract is that something like as shown below.

USER_ID       first_time            INFO_1       INFO_2         INFO_3    
USER_1        20220305010101        MALE         BANGKOK        BASEBALL
USER_2        20220306010101        FEMALE       TOKYO          SOCCER

Above table indicates that the latest updated values can be seen for INFO_1, INFO_2, INFO_3 and the oldest values for LOG_TIME for each user.

How to achieve this sql statement?

CodePudding user response：

The easiest way, and to avoid querying the table multiple times, would be to utilise analytic functions:

select distinct USR_ID,
       first_value(LOG_TIME) over(partition by USR_ID order by LOG_TIME) FIRST_TIME,
       first_value(INFO_1) over(partition by USR_ID order by if(info_1 is null,'',LOG_TIME) desc) INFO_1,
       first_value(INFO_2) over(partition by USR_ID order by if(info_2 is null,'',LOG_TIME) desc) INFO_2,
       first_value(INFO_3) over(partition by USR_ID order by if(info_3 is null,'',LOG_TIME) desc) INFO_3
from MY_TABLE;

Result:

Example Fiddle

Note, this assumes - given the columns are declared as nullable - blank values are indeed NULL.

CodePudding user response：

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6c6d699a9bcf1d6ab83784b3795a1da9

SELECT
    t1.USR_ID,
    MIN( LOG_TIME ) AS LOG_TIME,
    ( SELECT INFO_1 FROM MY_TABLE AS t2 WHERE t2.usr_id = t1.usr_id AND t2.info_1 != '' ORDER BY LOG_TIME DESC LIMIT 1 ) AS INFO_1,
    ( SELECT INFO_2 FROM MY_TABLE AS t2 WHERE t2.usr_id = t1.usr_id AND t2.info_2 != '' ORDER BY LOG_TIME DESC LIMIT 1 ) AS INFO_2,
    ( SELECT INFO_3 FROM MY_TABLE AS t2 WHERE t2.usr_id = t1.usr_id AND t2.info_3 != '' ORDER BY LOG_TIME DESC LIMIT 1 ) AS INFO_3 
FROM
    MY_TABLE AS t1 
GROUP BY
    t1.USR_ID

If the columns are NULL you should modify these conditions != '' with IS NOT NULL

CodePudding user response：

The easiest way to get the most recently updated values.

You must have a table with the Created and Updated dates.

Example: creation_date, update_date

In this way, you can detect the most recently updated data.