[SOLVED] How to get the last updated specific column values using mysql?

Issue

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?

Solution

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.

Answered By – Stu

Answer Checked By – Senaida (BugsFixing Volunteer)