title: SQL groupby argmax
body: <p>Let there be a table such as this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>group ID</th>
  <th>member ID</th>
  <th>value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>0</td>
  <td>3</td>
  <td>3</td>
  </tr>
  <tr>
  <td>0</td>
  <td>2</td>
  <td>5</td>
  </tr>
  <tr>
  <td>1</td>
  <td>4</td>
  <td>0</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>16</td>
  <td>0</td>
  </tr>
  <tr>
  <td>2</td>
  <td>21</td>
  <td>7</td>
  </tr>
  <tr>
  <td>2</td>
  <td>32</td>
  <td>4</td>
  </tr>
  <tr>
  <td>2</td>
  <td>14</td>
  <td>6</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>The table has three columns, a <code>group ID</code>, which uniquely indentifies a certain <code>group</code>, a <code>member ID</code>, which uniquely identifies <code>members</code> inside individual <code>groups</code> (globally, <code>member ID</code> is not unique), and <code>value</code>, which is something we want to maximize inside each group.</p>
  <p>The task is to find a <code>member ID</code> for each <code>group ID</code> which maximizes <code>value</code>. That sounds like a simple enough procedure, right?</p>
  <p>Well, apparently, it is not. I'm using MariaDB and have tried everything on <a href="https://stackoverflow.com/questions/4556653/is-there-something-equivalent-to-argmax-in-sql">this page</a>, such as</p>
  <pre><code>select group_id, member_id, value from table x
      join (select max(t.value) as max_v from table t group by group_id) y on y.max_v = x.value;
  </code></pre>
  <p>but none of these approaches yielded the correct result.
  For completeness, the expected output of the query for the above table would be</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>group ID</th>
  <th>member ID</th>
  <th>value</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>2</td>
  <td>5</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  <td>1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>21</td>
  <td>7</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>or even</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>group ID</th>
  <th>member ID</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>0</td>
  <td>2</td>
  </tr>
  <tr>
  <td>1</td>
  <td>2</td>
  </tr>
  <tr>
  <td>2</td>
  <td>21</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  </tr>
  <tr>
  <td>...</td>
  <td>...</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>if returning the <code>value</code> would cause problems. I'd appretiate any sort of help.</p>