L Riley L Riley - 6 months ago 10
SQL Question

SQL Group By, Get The row of the Min function

I know you cant Group By one column in a table (Although I wish it was that easy) but can I at last grab the fields of the row selected by the min?



<table class="tableizer-table">
<thead>
<tr class="tableizer-firstrow">
<th>prd_id</th>
<th>prd_description</th>
<th>Stock</th>
<th>Todays Order</th>
<th>Tomorrows Order</th>
<th>Next Batch</th>
<th>prd_status</th>
<th>Prd_line</th>
<th>prd_estimate_time</th>
<th>left_to_produce</th>
</tr>
</thead>
<tbody>
<tr>
<td>7</td>
<td>Product A</td>
<td>14</td>
<td>14</td>
<td>234</td>
<td>16249</td>
<td>New</td>
<td>2</td>
<td>17May16 17:54:02</td>
<td>16249</td>
</tr>
<tr>
<td>9</td>
<td>Product B</td>
<td>754</td>
<td>0</td>
<td>0</td>
<td>2128</td>
<td>New</td>
<td>5</td>
<td>17May16 18:11:28</td>
<td>2128</td>
</tr>
<tr>
<td>11</td>
<td>Product C</td>
<td>0</td>
<td>157</td>
<td>5106</td>
<td>6850</td>
<td>New</td>
<td>2</td>
<td>17May16 18:40:04</td>
<td>6850</td>
</tr>
<tr>
<td>17</td>
<td>Product D</td>
<td>1248</td>
<td>4296</td>
<td>9120</td>
<td>4728</td>
<td>New</td>
<td>8</td>
<td>18May16 04:50:09</td>
<td>9456</td>
</tr>
<tr>
<td>17</td>
<td>Product D</td>
<td>1248</td>
<td>4296</td>
<td>9120</td>
<td>4728</td>
<td>New</td>
<td>10</td>
<td>18May16 16:55:09</td>
<td>9456</td>
</tr>
</tbody>
</table>





Now for Product D all is the same except its produced on two lines and different times of the day.

I want one product per line with the lowest Estimated time displayed and the line its produced on

such as



<style type="text/css">
table.tableizer-table {
font-size: 12px;
border: 1px solid #CCC;
font-family: Arial, Helvetica, sans-serif;
}
.tableizer-table td {
padding: 4px;
margin: 3px;
border: 1px solid #CCC;
}
.tableizer-table th {
background-color: #104E8B;
color: #FFF;
font-weight: bold;
}
</style>
<table class="tableizer-table">
<thead>
<tr class="tableizer-firstrow">
<th>prd_id</th>
<th>prd_description</th>
<th>Stock</th>
<th>Todays Order</th>
<th>Tomorrows Order</th>
<th>Next Batch</th>
<th>prd_status</th>
<th>Prd_line</th>
<th>prd_estimate_time</th>
<th>left_to_produce</th>
</tr>
</thead>
<tbody>
<tr>
<td>7</td>
<td>Product A</td>
<td>14</td>
<td>14</td>
<td>234</td>
<td>16249</td>
<td>New</td>
<td>2</td>
<td>17May16 17:54:02</td>
<td>16249</td>
</tr>
<tr>
<td>9</td>
<td>Product B</td>
<td>754</td>
<td>0</td>
<td>0</td>
<td>2128</td>
<td>New</td>
<td>5</td>
<td>17May16 18:11:28</td>
<td>2128</td>
</tr>
<tr>
<td>11</td>
<td>Product C</td>
<td>0</td>
<td>157</td>
<td>5106</td>
<td>6850</td>
<td>New</td>
<td>2</td>
<td>17May16 18:40:04</td>
<td>6850</td>
</tr>
<tr>
<td>17</td>
<td>Product D</td>
<td>1248</td>
<td>4296</td>
<td>9120</td>
<td>4728</td>
<td>New</td>
<td>8</td>
<td>18May16 04:50:09</td>
<td>9456</td>
</tr>
</tbody>
</table>




Answer

Presuming sql-server (>= 2005) as rdbms you can use the ranking function ROW_NUMBER:

WITH CTE AS
(
   SELECT t.*, RN = ROW_NUMBER() OVER (PARTITION BY prd_id  -- add more columns if you want to group by multiple columns 
                                       ORDER BY prd_estimate_time ASC)
   FROM dbo.TableName
)
SELECT prd_id, prd_description, Stock, [Todays Order], [Tomorrows Order], [Next Batch], prd_status, Prd_line, prd_estimate_time, left_to_produce
FROM CTE
WHERE RN = 1