Get data from same row with MAX()?-CodePudding

[MariaDB 10.4, PHP8.0] How can I rewrite this so it works so it get weight_date from the row with MAX(total_weight) and list it AS highest_weight_date? I have read that we can't use MAX() among with WHERE? I have tested to rewrite several examples, but I give up and embarrassed show my latest try:

weight_date HAVING total_weight=MAX(total_weight) AS highest_weight_date

I try to add it to this but I get error. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=9c057570bd60cdf20a7148189a77fdc4

    SELECT *
           , LEAD(total_weight, 1) OVER(
               ORDER BY weight_date DESC
           ) AS prev_total_weight
           , LEAD(weight_date, 1) OVER(
               ORDER BY weight_date DESC
           ) AS prev_total_weight_date
    
           , MIN(total_weight) OVER() AS lowest_weight
           , MAX(total_weight) OVER() AS highest_weight
 
           , FROM_UNIXTIME(weight_date, '%u') AS weight_week
           , ROW_NUMBER() OVER(
              ORDER BY weight_date DESC
           ) AS RowNum      

    FROM   (
              SELECT *, weight_start_week   weight_end_week AS total_weight
              FROM   YourTable
           ) t
    ORDER BY RowNum

Thanks

CodePudding user response：

Your HAVING attempt makes it sound like you don't want all rows returned, but everything else in your question makes it sounds like you do, just with highest_weight_date in addition to all the other columns. Assuming the latter, you just want to add to your select:

FIRST_VALUE(weight_date) OVER (ORDER BY total_weight DESC) AS highest_weight_date

though I would recommend doing ORDER BY total_weight DESC, weight_date or ORDER BY total_weight DESC, weight_date DESC so you deterministically get either the first or last date with that weight, not an arbitrary one, when the weight occurs on more than one date.

CodePudding user response：

I have read that we can't use MAX() among with WHERE?

Maybe this helps: use a subquery to find the MAX() weight in the WHERE clause. Using your sample data ...

select from_unixtime( weight_date )
from YourTable
where ( weight_start_week   weight_end_week ) = ( 
  select max( weight_start_week   weight_end_week ) 
  from YourTable 
) ;

-- result
from_unixtime( weight_date )
2022-01-14 00:00:00

DBfiddle