Want to generate a row_number variable in MySQL 5.6 - but not using @variables-CodePudding

I have a table (all code is on fiddle).

id   nom    bal     bal_dt      val_dt
 1  Bill    75.00   2019-11-01  2020-03-31
 1  Bill    100.00  2020-04-01  2020-07-31
 1  Bill    500.00  2020-08-01  2021-11-11  -- record goes over New Year 2021
 2  Ben       5.00  2019-11-01  2020-03-31
 2  Ben      10.00  2020-04-01  2020-07-31
 2  Ben     100.00  2020-08-01  2021-11-11  -- record goes over New Year 2021
6 rows

The primary key is (id, bal_dt) - only one deposit/day.

I want to get the last record before the New Year 2021 (or <= 2021-01-01 00:00:00).

I try code from here as follow.

select a2.id, a2.nom, a2.val_dt,
(select count(*) from account a1 where a1.id < a2.id) AS rn 
from account a2
where a2.val_dt <= '2021-01-01 00:00:00'
order by val_dt desc;

But result is not good.

id   nom    val_dt      rn
 1  Bill    2020-07-31  0
 2  Ben     2020-07-31  3
 1  Bill    2020-03-31  0
 2  Ben     2020-03-31  3

I want something like

id nom  rn val_dt     bal
 1 Bill  1 2020-08-01 500.00
 2 Ben   1 2020-08-01 100.00

so I choose record for Bill and Ben. Any helps please?

note - I don't requier @variables and not assume 3 only records or only 2 accounts and not same dates and not only last date!

CodePudding user response：

You can use NOT EXISTS and a correlated subquery that checks for the absence of a younger timestamp within the desired period.

SELECT a1.id,
       a1.nom,
       a1.val_dt
       FROM account a1
       WHERE a1.val_dt < '2021-01-01 00:00:00'
             AND NOT EXISTS (SELECT *
                                    FROM account a2
                                    WHERE a2.val_dt < '2021-01-01 00:00:00'
                                          AND a2.val_dt > a1.val_dt
                                          AND a2.id = a1.id);

Note that 2021-01-01 00:00:00 already is in 2021, so the operator needs to actually be < not <=.

CodePudding user response：

Solved it (see fiddle)!

select
  tab.id, tab.md, a2.bal
from account a2
join
(
  select 
    a1.id, max(a1.bal_dt) AS md
  from account a1
  where a1.bal_dt <= '2021-01-01 00:00:00'
  group by a1.id
) as tab
on a2.id = tab.id and a2.bal_dt = tab.md;

and.

id          md  bal
1   2020-08-01  500.00
2   2020-08-01  100.00