I've looked at a various answers on stackoverflow for selecting the max value or replacing column values but I'm not can't seem to figure out how I could do both and return all the rows. Basically I want to return all rows but replace the value of a column in a given row if another row has a higher number in the same column and the same identifier. 

I'm not an SQL expert and this has me scratching my head and pulling my hair out... I'm hoping this can be done via a query without updating the data. Maybe I need to rework the data but this would be a huge manual task. Maybe I could do this in the view? I'd appreciate and be open to any suggestions for how to do this.

Below is an example of the view that is being queried. The **"code"** is the common field.

```
+-------+-------+-----------------------------------+   
| type1 | type2 | code | amt |                      |   
+-------+-------+-----------------------------------+  
| 1     | A     | 100  | 59                         |  
| 1     | B     | 200  | 75                         |  
| 2     | C     | 100  | 65   <-- Max for code 100  |  
| 2     | D     | 200  | 80   <-- Max for code 200  |  
| 3     | E     | 100  | 55                         |  
| 3     | F     | 200  | 70                         |  
+-------+-------+-----------------------------------+ 

```

I need to return all rows but replace the "amt" with the max value if the "code" is the same and the number is higher in another row. Here's an example of the output I'm looking for:

```
+-------+-------+------------------------------------------------+   
| type1 | type2 | code | amt |                                   |   
+-------+-------+------------------------------------------------+  
| 1     | A     | 100  | 65 <-- replaced w/max for code 100      |  
| 1     | B     | 200  | 80 <-- replaced w/max for code 200      | 
| 2     | C     | 100  | 65                                      |  
| 2     | D     | 200  | 80                                      |  
| 3     | E     | 100  | 65 <-- replaced w/max for code 100      |
| 3     | F     | 200  | 80 <-- replaced w/max for code 200      |
+-------+-------+------------------------------------------------+ 

```

The reason for trying this in a query is to keep the original data. Is this possible with a query or do I need to try to update the data instead? Many thanks!