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 |