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, 
UpdateTime_order
FROM staff
)
select 
Account, 
Score, 
UpdateDate,
ROW_NUMBER()OVER(PARTITION BY Account ORDER BY UpdateTime DESC) as UpdateTime_order3
from(
select *,
ROW_NUMBER()OVER(PARTITION BY Account, UpdateDate ORDER BY UpdateTime DESC) as UpdateTime_order2 
from CTE_staff
) jj
where jj.UpdateTime_order2=1


Account
Score
UpdateDate
UpdateTime_order3


K1897
A
2023-09-08
1

K1897
B
2023-08-01
2

K1897
B
2023-06-28
3

K1897
B
2023-06-05
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_order3
K1897	A	2023-09-08	1
K1897	B	2023-08-01	2
K1897	B	2023-06-28	3
K1897	B	2023-06-05	4
K1898	C	2023-06-04	1