MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version()


8.0.30


CREATE TABLE mytable(
   id  INTEGER  NOT NULL 
  ,dt  DATE  NOT NULL
  ,val INTEGER  NOT NULL
);
INSERT INTO mytable(id,dt,val) VALUES (1,'2022-08-10',10);
INSERT INTO mytable(id,dt,val) VALUES (1,'2022-08-12',11);
INSERT INTO mytable(id,dt,val) VALUES (1,'2022-08-08',15);
INSERT INTO mytable(id,dt,val) VALUES (1,'2022-08-16',9);
INSERT INTO mytable(id,dt,val) VALUES (2,'2022-07-02',2);
INSERT INTO mytable(id,dt,val) VALUES (2,'2022-07-01',4);
INSERT INTO mytable(id,dt,val) VALUES (2,'2022-06-30',7);
    select t.*,
        first_value(val) over(partition by id order by dt desc) last_val,
        max(val) over(partition by id) max_val
    from mytable t
    order by id, dt


id
dt
val
last_val
max_val


1
2022-08-08
15
9
15

1
2022-08-10
10
9
15

1
2022-08-12
11
9
15

1
2022-08-16
9
9
15

2
2022-06-30
7
2
7

2
2022-07-01
4
2
7

2
2022-07-02
2
2
7

id	dt	val	last_val	max_val
1	2022-08-08	15	9	15
1	2022-08-10	10	9	15
1	2022-08-12	11	9	15
1	2022-08-16	9	9	15
2	2022-06-30	7	2	7
2	2022-07-01	4	2	7
2	2022-07-02	2	2	7