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 |