Select or Update only the latest records per group or set in MySQL database table

So I faced this today that I could have keep a column of TinyInt type in a table where I keep multiple records for a, lets say, user or product. Such as, user login history. 

The task is to load only the latest records per user. To do that I can run a query like this one

SELECT *
FROM user_login_history t1
WHERE (user_id, create_date_time_int) IN (
    SELECT user_id, MAX(create_date_time_int) AS latest_timestamp
    FROM user_login_history t2
    WHERE t1.user_id = t2.user_id
    GROUP BY user_id
);

But we could have just put a column of TinyInt (default 0) and name it is_latest. So when is_latest is 1 the record is latest and otherwise not latest. So we could have selected with a much simpler and efficient query (set an index on is_latest)

SELECT * FROM user_login_history WHERE is_latest = 1;

To do this, first we have to alter the table to add the is_latest column. 

ALTER TABLE `sample_database`.`user_login_history` 
ADD COLUMN `is_latest` TINYINT UNSIGNED NULL DEFAULT 0 COMMENT '1=yes,0=no' AFTER `create_date_time_int`;

Now lets add an index to this column

ALTER TABLE `sample_database`.`user_login_history` 
ADD INDEX `latest` (`is_latest` ASC) VISIBLE;

Now the task is to set 1 for is_latest for the latest record for each user. To do that, we have to make use of temporary table. 

CREATE TEMPORARY TABLE temp_latest_entries
SELECT id
FROM user_login_history t1
WHERE (user_id, create_date_time_int) IN (
    SELECT user_id, MAX(create_date_time_int) AS latest_timestamp
    FROM user_login_history t2
    WHERE t1.user_id= t2.user_id
    GROUP BY user_id
);

UPDATE user_login_history 
SET is_latest = 1
WHERE id IN (SELECT id FROM temp_latest_entries);

DROP TEMPORARY TABLE IF EXISTS temp_latest_entries;

Here we are running 3 queries.

In the first query we are selecting ID of the latest record for each user_id from the user_login_history table and inserting them in the temporary table named temp_latest_entries

Then we update the user_login_history table for the IDs we have in the temporary table.

And then we just drop or delete the temporary table as we don't need it anymore.

So from now on we can simply select the records with is_latest = 1 when we need to select only the latest records per user_id

Bonus:

As new records will keep entering into the table as users keep logging in and out. We need to run an extra query when we insert a record in the user_login_history table.

First of all, we have to set is_latest = 1 for each new record we insert.

But then 2 records for the user_id will have is_latest = 1, which must not be, only the latest record should have is_latest = 1. 

It can be handled in 2 ways.

First one is that after inserting the latest record we have to set is_latest = 0 for the last latest record. We can do it as follows

UPDATE user_login_history SET is_latest = 0 WHERE user_id = :this_user_id AND id <> :new_record_id;

Here :new_record_id is the autoincrement id that we get after inserting the new latest record. So we set is_latest = 0 for all the records where user_id is this current user id and the id of the row is not the one we just created or inserted.

Second method would be to first set is_latest = 0 for all the records for this user_id and then insert the new record with is_latest = 1. So the update query would look like this

UPDATE user_login_history SET is_latest = 0 WHERE user_id = :this_user_id;

Then we insert the new record with is_latest = 1;