title: Max Row In Group By
body: <p>I've tried reading through a number of the solutions I've found, but there seem to be slight differences in the problems where I'm not sure how to adapt them to fit my own. </p>
  
  <p>I have a table of transactions like so:</p>
  
  <pre><code>+------+-------+------------------------------+
  | id   | rev_$ | product  | local_currency    |
  +------+-------+------------------------------+
  | 1    | 15    | shoe     | USD               |
  | 2    | 10    | shirt    | USD               |
  | 1    | 20    | shoe     | CAD               |
  | 2    | 30    | shoe     | GBP               |
  | 1    |  8    | shirt    | USD               |
  | 2    | 15    | shirt    | USD               |
  | 1    | 10    | shoe     | CAD               |
  | 2    | 10    | shoe     | USD               |
  +------+-------+------------------------------+
  </code></pre>
  
  <p>I want to aggregate the table so that</p>
  
  <ul>
  <li>I get the total for each ID by product</li>
  <li>The <code>local_currency</code> is the currency used for the highest single value transaction (as well as other fields that I haven't included)</li>
  </ul>
  
  <p>So that table should look like this, after aggregating:</p>
  
  <pre><code>+------+-------+------------------------------+
  | id   | rev_$ | product  | local_currency    |
  +------+-------+------------------------------+
  | 1    | 45    | shoe     | CAD               |
  | 1    |  8    | shirt    | USD               |
  | 2    | 25    | shirt    | USD               |
  | 2    | 40    | shoe     | GBP               |
  +------+-------+------------------------------+
  </code></pre>
  
  <p>Similar questions: <a href="https://stackoverflow.com/questions/15455368/select-rows-with-max-value-from-groups-of-rows-grouped-by-multiple-columns-psql">(1)</a>, <a href="https://stackoverflow.com/questions/1313120/retrieving-the-last-record-in-each-group-mysql">(2)</a></p>