How to get the Max value else latest max value

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 :

Can anyone suggest me on this one?

Using ROW_NUMBER we can try:

<!-- language: sql -->

WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY ACCOUNT ORDER BY DATE DESC) rn
    FROM yourTable
    WHERE VALUE <> 0
)

SELECT DATE, ACCOUNT, VALUE
FROM cte
WHERE rn = 1;

On earlier versions of MySQL:

<!-- language: sql -->

SELECT t1.DATE, t1.ACCOUNT, t1.VALUE
FROM yourTable t1
INNER JOIN
(
    SELECT ACCOUNT, MAX(DATE) AS MAX_DATE
    FROM yourTable
    WHERE VALUE <> 0
    GROUP BY ACCOUNT
) t2
    ON t2.ACCOUNT = t1.ACCOUNT AND
       t2.MAX_DATE = t1.DATE
WHERE
    t1.VALUE <> 0;

This can be done using this query :

The cte was used to get accounts with values, then we need to get the maximum value and the related date for accounts with value = 0, and finally we use union all to combine those datasets.

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

with cte as (
  select ACCOUNT, DATE, max(VALUE) as max_value
  from mytable
  where DATE = @curdate
  group by ACCOUNT, DATE
)
select ACCOUNT, DATE, max_value as VALUE
from cte where max_value > 0
union all
select t.ACCOUNT, t.DATE, t.VALUE
from mytable t
inner join (
  select t.ACCOUNT, max(t.VALUE) as VALUE
  from cte c
  inner join mytable t on t.ACCOUNT = c.ACCOUNT
  where max_value = 0
  group by t.ACCOUNT
) as s on s.ACCOUNT = t.ACCOUNT and s.VALUE = t.VALUE

Result :

Demo here

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