Here’s how you could do it with a user-defined aggregate.
DELIMITER //
CREATE FUNCTION max_by_init() RETURNS
RECORD(dt DATETIME(6), val DOUBLE) AS
BEGIN
RETURN ROW(NULL, NULL);
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION max_by_iter(
state RECORD(dt DATETIME(6), val DOUBLE),
dt datetime(6),
val DOUBLE) RETURNS RECORD(dt DATETIME(6), val DOUBLE)
AS
BEGIN
RETURN ROW(
if(val > state.val, dt, state.dt),
if(val > state.val, val, state.val));
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION max_by_merge(state1 RECORD(dt DATETIME(6), val DOUBLE),
state2 RECORD(dt DATETIME(6), val DOUBLE))
RETURNS RECORD(dt DATETIME(6), val DOUBLE) AS
BEGIN
RETURN row(if(state1.val > state2.val, state1.dt, state2.dt),
if(state1.val > state2.val, state1.val, state2.val));
END //
DELIMITER ;
DELIMITER //
CREATE FUNCTION max_by_terminate(state RECORD(dt DATETIME(6), val DOUBLE))
RETURNS DATETIME(6) AS
BEGIN
RETURN state.dt;
END //
DELIMITER ;
CREATE AGGREGATE max_by(DATETIME(6), DOUBLE) RETURNS DATETIME(6)
WITH STATE RECORD(dt DATETIME(6), val DOUBLE)
INITIALIZE WITH max_by_init
ITERATE WITH max_by_iter
MERGE WITH max_by_merge
TERMINATE WITH max_by_terminate;
-- test
create table ts(d datetime(6), v double);
insert ts values(now(6), 1);
insert ts values(now(6), 2);
insert ts values(now(6), 3);
insert ts values(now(6), 2);
insert ts values(now(6), 1);
select * from ts order by d;
+----------------------------+------+
| d | v |
+----------------------------+------+
| 2021-01-04 16:39:52.411670 | 1 |
| 2021-01-04 16:39:57.351114 | 2 |
| 2021-01-04 16:40:04.794208 | 3 |
| 2021-01-04 16:40:13.367547 | 2 |
| 2021-01-04 16:40:19.969650 | 1 |
+----------------------------+------+
select max(v), max_by(d, v)
from ts;
+--------+----------------------------+
| max(v) | max_by(d, v) |
+--------+----------------------------+
| 3 | 2021-01-04 16:40:04.794208 |
+--------+----------------------------+