Skip to content
Advertisement

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:

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

Thanks

Advertisement

Answer

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:

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

User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement