Jason-299789 (10/30/2012)
heres a quick and dirty way
CREATE Table #results
(
RecordId Int
,ItemId Int
,cost Decimal(38,2)
)
Insert into #results
values (43392, 3880 ,50.00)
,(39056, 3881, 93.75)
,(33941, 3881, 97.50)
,(33970, 3881, 97.50)
,(38950, 3881, 98.75)
,(99999, 3881, 98.75)
,(40417, 3881, 96.56)
,(36491, 3882, 30.00)
,(37265, 3883, 6.50)
,(35054, 3883 ,4.50)
,(33658, 3884, 14.94)
,(36585, 3884,18.77)
,(33134, 3884, 14.75)
Select r.RecordId,r.ItemId,r.cost
From #results r
JOIN (Select ItemId,MAX(cost) mcost
from #results
group by ItemId) m
on m.ItemId=r.ItemId
and mcost=r.cost
This assumes that the Max(Cost) per Item Id is unique.
Eg if you added a row 9999,3881,98.75 and ran this you would get two rows in the output
36585388418.77
3726538836.50
36491388230.00
38950388198.75
99999388198.75
43392388050.00
I dont know if thats what you want.
Sorry no i need to return only one result per ItemID. No preference on which one just need one result. I now see why you are after the DDL and DML.