Question 183796 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – How to get the Max value else latest max value

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?

Tags:

3

Answers


  1. 0

    Using ROW_NUMBER we can try:

    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:

    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;
    Login or Signup to reply.
  2. 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 :

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

    Demo here

    Login or Signup to reply.
  3. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search