How to write a Mysql Generated Column that returns true if it is the most recent entry?

user_18371648 0
Viewed 29

My Mysql table looks like this currently:

id time
1 2011-12-12 09:27:24
1 2011-12-13 09:27:31
1 2011-12-14 09:27:34
2 2011-12-14 09:28:21

and I would like to add a virtual generated column returning a boolean. This boolean would be true if it is the most recent entry for a given id.

id time is_last_entry
1 2011-12-12 09:27:24 0
1 2011-12-13 09:27:31 0
1 2011-12-14 09:27:34 1
2 2011-12-14 09:28:21 1

How should I write this statement?

CREATE TABLE test(
    id INT NOT NULL,
    time DATETIME NOT NULL,
    is_last_entry TINYINT GENERATED ALWAYS AS [=> please complete here]
);
mysql sql
1 Answers

A generated column can only have an expression that references columns in the same row, so it can't determine if the row has the greatest time value among all the other rows. A generated column also cannot use subqueries or window functions or any of the other means by which you could compare values in other rows.

One comment above suggests using a trigger, but this won't work in MySQL because you can't do an update against the same table for which the trigger was spawned.

Example:

mysql> create trigger t before insert on test for each row 
  update test set is_last_entry = 0 where time < NEW.time;

mysql> insert into test (id time) values (1, now());
ERROR 1442 (HY000): Can't update table 'test' in stored function/trigger 
  because it is already used by statement which invoked this stored function/trigger.

You have two options:

  1. After you insert/update/delete rows in this table, you have to do additional updates explicitly to set the boolean column on the latest row.

  2. Don't store the column for the boolean at all, but use a window function when you query, so the result of the query is always guaranteed to be current:

    mysql> select id, time, time = max(time) over() as is_last_entry from test;
+----+---------------------+---------------+
| id | time                | is_last_entry |
+----+---------------------+---------------+
|  1 | 2022-07-19 08:32:32 |             1 |
|  2 | 2020-06-06 00:00:00 |             0 |
+----+---------------------+---------------+
Bill_Karwin_208600
Related