I have a calculated field that varies by customer. I need to get the min of the calculated fields for that customer, plus additional field at the record. I think this is a common pattern & I think I should be able to create an inner join back to get the add'l values.
The #temp is the table of values I pull back from another stored procedure: Select MIN(ydiff)as Nearest, Customer from ( Select * from #temp ) T Group by Customer
EXAMPLE :
MIN for FRED 0.1080322 0.1080322 FRED 0.01831055 JACK
& the #Temp
FRED ITEM X -6.66314 ITEM Y 29.73999 0.260009766 FRED ITEM X -6.752899 ITEM Y 30.10803 0.108032227 FRED ITEM X -6.842802 ITEM Y 30.43213 0.432128906
The red line is what needs to be found & must match both customer & the min value. and the min for all other customers and their respective rpws....
SELECT t.*
FROM #temp t
INNER JOIN
(
Select MIN(ydiff)as Nearest, Customer
from #temp
Group by Customer
)t1
ON t1.Customer = t.Customer
AND t1.Nearest = t.ydiff
------------------------------------------------------------------------------------------------------ SQL Server MVP http://visakhm.blogspot.com/
innerjoin to get rest of fields.with function
Posted - 05/21/2012 : 22:06:18
