SQL Server 2014

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

CREATE TABLE client_balance
(
    id INTEGER,
    balance_date_start DATETIME,
    balance_start INTEGER,
    balance_date_mid DATETIME,
    balance_mid INTEGER,
);
insert into client_balance values ( 676, '2021-10-02', 255, '2021-10-17', 255 );
insert into client_balance values ( 155, '2021-10-02', 222, '2021-10-15', 197 );
insert into client_balance values ( 676, '2021-09-01', 211, '2021-09-14', 333 );    
insert into client_balance values ( 155, '2021-11-01', 159, '2021-11-15', 104 );
4 rows affected

select * from client_balance;


id
balance_date_start
balance_start
balance_date_mid
balance_mid


676
2021-10-02 00:00:00.000
255
2021-10-17 00:00:00.000
255

155
2021-10-02 00:00:00.000
222
2021-10-15 00:00:00.000
197

676
2021-09-01 00:00:00.000
211
2021-09-14 00:00:00.000
333

155
2021-11-01 00:00:00.000
159
2021-11-15 00:00:00.000
104


SELECT
    id,
    balance_date_start,
    balance_start,
    balance_date_mid,
    balance_mid
  FROM (
    SELECT
        cb.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date_start DESC) r
      FROM client_balance cb
    ) t
  WHERE r = 1
;


id
balance_date_start
balance_start
balance_date_mid
balance_mid


155
2021-11-01 00:00:00.000
159
2021-11-15 00:00:00.000
104

676
2021-10-02 00:00:00.000
255
2021-10-17 00:00:00.000
255

id	balance_date_start	balance_start	balance_date_mid	balance_mid
676	2021-10-02 00:00:00.000	255	2021-10-17 00:00:00.000	255
155	2021-10-02 00:00:00.000	222	2021-10-15 00:00:00.000	197
676	2021-09-01 00:00:00.000	211	2021-09-14 00:00:00.000	333
155	2021-11-01 00:00:00.000	159	2021-11-15 00:00:00.000	104