title: SQL - Selecting Highest Quantity Category per hour
body: <p>I Have a table with the following columns: 1) hours [Values between 0-47] 2) Categories (Produce, Meat, Dairy) 3) Avg(Quantity). This table follows a query that has already been selected for 4 weeks of time.</p>
  <p>How do I select the highest average quantity category for each hour? For each hour I want the best performing category. Not the best performance of each category within the hour.</p>
  <p>Problem is each attempt either removes the category, hour, returns all of the categories for each hour portion, or doesn't select the max value of each category at all.</p>
  <p>What I tried:</p>
  <pre class="lang-sql prettyprint-override"><code>Select hour_key, category, Avg(Quantity)
  From Step2Time 
  Group by hour_key, category
  </code></pre>
  <p>Original Table:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Hour</th>
  <th style="text-align: center;">Category</th>
  <th style="text-align: center;">AVG Quantity</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">0</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: center;">123</td>
  </tr>
  <tr>
  <td style="text-align: left;">23</td>
  <td style="text-align: center;">Meat</td>
  <td style="text-align: center;">16</td>
  </tr>
  <tr>
  <td style="text-align: left;">45</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: center;">11</td>
  </tr>
  </tbody>
  </table>
  </div>
  <blockquote>
  </blockquote>
  <p>Returned Results:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Hour</th>
  <th style="text-align: center;">Category</th>
  <th style="text-align: center;">Quantity</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">0</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: center;">15</td>
  </tr>
  <tr>
  <td style="text-align: left;">0</td>
  <td style="text-align: center;">Meat</td>
  <td style="text-align: center;">16</td>
  </tr>
  <tr>
  <td style="text-align: left;">0</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: center;">13</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>Expected Results:</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th style="text-align: left;">Hour</th>
  <th style="text-align: center;">Category</th>
  <th style="text-align: right;">Quantity</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td style="text-align: left;">0</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: right;">15</td>
  </tr>
  <tr>
  <td style="text-align: left;">1</td>
  <td style="text-align: center;">Meat</td>
  <td style="text-align: right;">8</td>
  </tr>
  <tr>
  <td style="text-align: left;">2</td>
  <td style="text-align: center;">Dairy</td>
  <td style="text-align: right;">16</td>
  </tr>
  </tbody>
  </table>
  </div>