CODEWITHSUNDEEP
sqlmariadbgroupwise-maximummariadb-10.4
bollob
bollob

Reputation: 37

Get data from same row with MAX()?

[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

Upvotes: 0

Views: 65

Answers (2)

ysth
ysth

Reputation: 98508

To get the highest_weight_date (the weight_date from the row that has the highest total_weight), 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.

fiddle

Upvotes: 1

stefan
stefan

Reputation: 2252

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

Upvotes: 0

Related Questions