How to get the Max value else latest max value

Question

DATE	ACCOUNT	VALUE
2023-06-06	1234	100
2023-06-07	1234	120
2023-06-08	1234	80
2023-06-06	3456	40
2023-06-07	3456	60
2023-06-08	3456	80
2023-06-05	5648	600
2023-06-06	5648	800
2023-06-06	5648	650
2023-06-07	5648	0
2023-06-08	5648	0

I'm passing current date and getting the latest value from the table.

 set @curdate = '2023-06-08 ';
 select DATE,ACCOUNT,
        CASE WHEN DATE = @curdate THEN VALUE 
             WHEN VALUE = 0 AND DATE = @curdate THEN MAX(VALUE)
             ELSE 0
        END AS MAX_VALUE
from table ;

Actually I'm trying to get the latest value and for example if value is not present for current date then I have to get last maximum value for that account.

output :

DATE	ACCOUNT	VALUE
2023-06-08	1234	80
2023-06-08	3456	80
2023-06-06	5648	650

Can anyone suggest me on this one?

Answer 1

To get the first previous value that is not equal to 0,(when the value on @curdate is equal to 0 or not exists) we could use a subquery and aggregate as the following:

set @curdate = '2023-06-08';

select max(DATE) as 'DATE',
       ACCOUNT,
       coalesce(nullif(max(case when DATE = @curdate then VALUE end), 0), 
                (select value from table_name d 
                 where d.ACCOUNT = t.ACCOUNT and
                       d.DATE < @curdate and
                       d. value > 0
                 order by d.DATE DESC
                 limit 1)
               ) MAX_VALUE
from table_name t
WHERE DATE <= @curdate and value > 0
group by ACCOUNT

Demo

Another approach, for mysql 5.7 you could simulate the row_number functionality as the following:

set @curdate = '2023-06-08';
set @rn =0;
set @acc = null;

select DATE, ACCOUNT, value 
  from
(
  select *,
     if(@acc<>ACCOUNT, @rn:=1, @rn:=@rn+1) rn,
     @acc:=ACCOUNT 
  from table_name 
  where DATE <= @curdate and value > 0
  order by ACCOUNT, DATE desc
) t
where rn = 1

Demo