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 Account, MAX(UpdateTime) AS MAX_UpdateTime
FROM staff
GROUP BY Account, CAST(UpdateTime AS DATE)


Account
MAX_UpdateTime


K1898
2023-06-04 14:20:13.790

K1897
2023-06-05 14:20:13.790

K1897
2023-06-28 07:23:57.697

K1897
2023-08-01 10:07:57.487

K1897
2023-09-08 14:57:58.113


SELECT s.*
FROM staff s
INNER JOIN (
  SELECT Account, MAX(UpdateTime) AS MAX_UpdateTime
  FROM staff
  GROUP BY Account, CAST(UpdateTime AS DATE)
) AS t ON S.Account = t.Account AND s.UpdateTime = t.MAX_UpdateTime
ORDER BY s.UpdateTime DESC


Account
Score
UpdateTime
UpdateTime_order


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

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


SELECT Account, Score, UpdateTime, UpdateTime_order
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY Account, CAST(UpdateTime AS Date) ORDER BY UpdateTime DESC) AS RN
  FROM staff
) AS S
WHERE rn = 1
ORDER BY UpdateTime DESC


Account
Score
UpdateTime
UpdateTime_order


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

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	MAX_UpdateTime
K1898	2023-06-04 14:20:13.790
K1897	2023-06-05 14:20:13.790
K1897	2023-06-28 07:23:57.697
K1897	2023-08-01 10:07:57.487
K1897	2023-09-08 14:57:58.113