SQL Server 2014

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

CREATE TABLE balance
(
    id INTEGER,
    balance_date DATETIME,
    balance INTEGER,
);
insert into balance values ( 1, '2018-10-01', 111 );
insert into balance values ( 1, '2018-10-02', 115 );
insert into balance values ( 12, '2017-10-01', 1134 );    
insert into balance values ( 12, '2017-09-01', 159 );
insert into balance values ( 1, '2018-09-02', 130 );
insert into balance values ( 5, '2018-09-02', 150 );
6 rows affected

select * from balance;


id
balance_date
balance


1
2018-10-01 00:00:00.000
111

1
2018-10-02 00:00:00.000
115

12
2017-10-01 00:00:00.000
1134

12
2017-09-01 00:00:00.000
159

1
2018-09-02 00:00:00.000
130

5
2018-09-02 00:00:00.000
150


WITH bal AS (
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b
)
SELECT id, balance_date, balance FROM bal WHERE r = 1 or r = 2
  ORDER BY id, balance_date desc
;


id
balance_date
balance


1
2018-10-02 00:00:00.000
115

1
2018-10-01 00:00:00.000
111

5
2018-09-02 00:00:00.000
150

12
2017-10-01 00:00:00.000
1134

12
2017-09-01 00:00:00.000
159


-- ПОСМОТРИТЕ как работает функция ROW_NUMBER() !!!
    SELECT
        b.*,
        row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
      FROM balance b


id
balance_date
balance
r


1
2018-10-02 00:00:00.000
115
1

1
2018-10-01 00:00:00.000
111
2

1
2018-09-02 00:00:00.000
130
3

5
2018-09-02 00:00:00.000
150
1

12
2017-10-01 00:00:00.000
1134
1

12
2017-09-01 00:00:00.000
159
2

id	balance_date	balance
1	2018-10-01 00:00:00.000	111
1	2018-10-02 00:00:00.000	115
12	2017-10-01 00:00:00.000	1134
12	2017-09-01 00:00:00.000	159
1	2018-09-02 00:00:00.000	130
5	2018-09-02 00:00:00.000	150