title: SQL: Select the largest value for a group
body: <p>I have a table like this:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>someOtherCols</th>
  <th>shelf</th>
  <th>type</th>
  <th>count</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>...</td>
  <td>row1</td>
  <td>A</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>row1</td>
  <td>B</td>
  <td>3</td>
  </tr>
  <tr>
  <td>...</td>
  <td>row2</td>
  <td>C</td>
  <td>2</td>
  </tr>
  <tr>
  <td>...</td>
  <td>row2</td>
  <td>D</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like to group by shelf, and only keep the type that has the highest count. If there's a tie, choose any row.</p>
  <p>So the result would be like:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>someOtherCols</th>
  <th>shelf</th>
  <th>type</th>
  <th>count</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>...</td>
  <td>row1</td>
  <td>B</td>
  <td>3</td>
  </tr>
  <tr>
  <td>...</td>
  <td>row2</td>
  <td>C</td>
  <td>2</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I'm using AWS Athena and I'm trying the following query that I saw from another answer I saw:</p>
  <pre><code>SELECT * FROM table
  WHERE count IN (MAX(count) FROM shoppingAggregate GROUP BY someOtherCols, shelf)
  </code></pre>
  <p>Seems like Athena does not like it. How can I achieve this? Thanks a lot!</p>