SQL Server 2022

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

CREATE TABLE staff (
    Account VARCHAR(15),
    Score VARCHAR(2),
    UpdateTime DATETIME,
    UpdateTime_order INT
);
INSERT INTO staff (Account, Score, UpdateTime, UpdateTime_order)
VALUES 
('K1897',     'A',       '2023-09-08 14:57:58.113',   '1'),
('K1897',     'B',       '2023-09-08 14:57:57.896',   '2'),
('K1897',     'B',       '2023-08-01 10:07:57.487',   '3'),
('K1897',     'B',       '2023-06-28 07:23:57.696',   '4'),
('K1897',     'B',       '2023-06-05 14:20:13.789',   '5'),
('K1898',     'C',       '2023-06-04 14:20:13.789',   '1')  ;
6 rows affected

select *
from staff


Account
Score
UpdateTime
UpdateTime_order


K1897
A
2023-09-08 14:57:58.113
1

K1897
B
2023-09-08 14:57:57.897
2

K1897
B
2023-08-01 10:07:57.487
3

K1897
B
2023-06-28 07:23:57.697
4

K1897
B
2023-06-05 14:20:13.790
5

K1898
C
2023-06-04 14:20:13.790
1


;with cte_staff as
  (select Account, Score, CAST([UpdateTime] AS Date) UpdateDate, UpdateTime_order,
      row_number() over (partition by Account, cast([UpdateTime] as Date) order by updateTime desc) rn
   from staff
  )
select Account, Score, UpdateDate, 
  row_number() over (partition by Account order by Score, rn) UpdateTime_order
from cte_staff
where rn = 1
order by Account, UpdateTime_order; 


Account
Score
UpdateDate
UpdateTime_order


K1897
A
2023-09-08
1

K1897
B
2023-06-05
2

K1897
B
2023-06-28
3

K1897
B
2023-08-01
4

K1898
C
2023-06-04
1

Account	Score	UpdateTime	UpdateTime_order
K1897	A	2023-09-08 14:57:58.113	1
K1897	B	2023-09-08 14:57:57.897	2
K1897	B	2023-08-01 10:07:57.487	3
K1897	B	2023-06-28 07:23:57.697	4
K1897	B	2023-06-05 14:20:13.790	5
K1898	C	2023-06-04 14:20:13.790	1

Account	Score	UpdateDate	UpdateTime_order
K1897	A	2023-09-08	1
K1897	B	2023-06-05	2
K1897	B	2023-06-28	3
K1897	B	2023-08-01	4
K1898	C	2023-06-04	1