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
hidden batch(es)
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
…
hidden batch(es)
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
…
hidden batch(es)
-- ПОСМОТРИТЕ как работает функция ROW_NUMBER() !!!
SELECT
b.*,
row_number() OVER (PARTITION BY id ORDER BY balance_date DESC) r
FROM balance b
