innerjoin to get rest of fields.with function

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

innerjoin to get rest of fields.with function

Author

Topic

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-05-21 : 22:06:18

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....

Thanks ...

andrewcw

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2012-05-21 : 22:15:12

[code]
select *
from
(
select *, rn = row_number() over (partition by customer order by ydiff)
from #temp
) d
where d.rn = 1
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-21 : 22:18:27

[code]
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
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

andrewcw
Posting Yak Master

133 Posts

Posted - 2012-05-21 : 22:52:04

Thank you very much !!!! Works !!!

andrewcw

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources