Return other columns corresponding to one column minimum
<p>Please someone help with this query:</p>

<p>Query table1 and return all table1.parentOrderNumber, table1.orderPriority, table1.orderQuantity values where the corresponding table2.orderNumber equals table1.parentOrderNumber and the lowest value table2.operationNumber has an table2.scheduledStartDate greater than or equal to the current date -5 days and less than or equal to the current date + 3 days. </p>

<p>Here table2 has duplicate orderNumber which have different operationNumber against them. I have tried having clause and others but it doesnt seem to help. I have written following query so far which is returning duplicate parentOrderNumber and not only for minimum value of operationNumber:</p>

<pre><code>SELECT 
    t1.parentOrderNumber AS parentOrderNumber
    ,t2.scheduledStartDate AS scheduledStartDate
    ,t1.salesOrderNumber AS salesOrderNumber
    ,t1.salesOrderLine AS salesOrderLine
    ,t1.orderPriority AS orderPriority
    ,t1.orderQuantity AS orderQuantity
    ,t1.modelNumber AS modelNumber
    ,t2.calculatedActualLabor AS CalculatedActualLabor
    ,MIN(t2.operationNumber) AS operationNumber
FROM
    table2 t2 WITH(NOLOCK)
LEFT JOIN 
    table1 t1 WITH(NOLOCK) ON t2.orderNumber = t1.parentorderNumber
WHERE 
    parentOrderNumber &lt;&gt; '' 
    AND t1.productionplant = 'xyz'
GROUP BY 
    --t1.orderNumber
    t1.parentOrderNumber
    ,t2.scheduledStartDate
    ,t1.salesOrderNumber
    ,t1.salesOrderLine
    ,t1.orderPriority
    ,t1.orderQuantity
    ,t1.modelNumber
    ,t2.calculatedActualLabor
</code></pre>