0

I am able to find the row with the maximum value for a Value field in an given set of records with the same name using

Select Name, Max(Value) from table group by Name, Value

which returns to me the record with the highest value but I am looking to turn this into an update so I can

  • Flag the record with the highest value in a IsMaxValue
  • For each record in the Name, Value group store the highest value found in a 'MaxValue' field

Simple select version is here:

http://sqlfiddle.com/#!9/ccd32/5

with fields ready for updates as per above if it is possible.

Mark H
  • 259
  • 2
  • 10
  • Possible duplicate of [MySQL - UPDATE query based on SELECT Query](http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query) – Lucia Pasarin Oct 06 '15 at 21:54
  • Is this what you want? http://sqlfiddle.com/#!9/d3523/1 – jpw Oct 06 '15 at 22:00
  • @jpw the output is yes though I can't see how yours differs. Ah my bad I need to learn to use the scroll bar. Thank you. – Mark H Oct 06 '15 at 23:49
  • @MarkH the update query is in the left pane of the fiddle. If this gives the result you want I'll post it as an answer. – jpw Oct 06 '15 at 23:52
  • 1
    Don't store derived data – Strawberry Oct 07 '15 at 00:00
  • @MarkH So, did my proposed solution work for you? If so, please consider accepting the answer. – jpw Oct 12 '15 at 11:13

1 Answers1

0

I believe this statement is what you might be looking for:

update maxvalues
join (
  Select Color, Max(`Value`) max_value 
  from MaxValues 
  group by Color
) a on maxvalues.color = a.color and value = a.max_value
set ismaxrecord = '1', maxrecordid = a.max_value;

Sample SQL Fiddle

Given your sample data the table would look like below after the update:

|  Color | Value | IsMaxRecord | MaxRecordID |
|--------|-------|-------------|-------------|
| Orange |     1 |             |           0 |
| Orange |     2 |             |           0 |
| Orange |     3 |           1 |           3 |
|  Black |    30 |           1 |          30 |
|  Black |    20 |             |           0 |
|  Black |    10 |             |           0 |
jpw
  • 44,361
  • 6
  • 66
  • 86