clear markdown compare help best fiddles feedback
clear markdown feedback
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 3601395 fiddles created (48008 in the last week).

CREATE TABLE balance ( id INTEGER, balance_date DATETIME, balance INTEGER, );
 hidden batch(es)


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 ), last_bal AS ( SELECT id, balance_date, balance FROM bal WHERE r = 1 ), prev_bal AS ( SELECT id, balance_date, balance FROM bal WHERE r = 2 ) SELECT lb.id, lb.balance_date AS last_balance_date, lb.balance AS last_balance, pb.balance_date AS prev_balance_date, pb.balance AS prev_balance FROM last_bal lb LEFT JOIN prev_bal pb ON pb.id = lb.id ;
id last_balance_date last_balance prev_balance_date prev_balance
1 2018-10-02 00:00:00.000 115 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 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
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
 hidden batch(es)