July 20, 2011 at 7:17 am
Hi i have some of record from which i need only Min Rate Record. Code is below
SupplierID MaterialID Rate
208 233 58.50
571 233 59.35
115 235 120.00
208 235 124.40
1941 218 4.45
664 252 232.15
262 252 231.00
i want to get.
SupplierID MaterialID Rate
208 233 58.50
115 235 120.00
1941 218 4.45
262 252 231.00
i am using :
SELECT TOP (100) PERCENT SupplierID, MaterialID, MIN(Rate) AS Rate
FROM SupplierRate
WHERE (Rate > 0)
GROUP BY SupplierID, MaterialID
ORDER BY MaterialID
not getting my record. Any help would be appreciated.
July 20, 2011 at 7:22 am
Since you want columns not included in the MIN function, you'll need to use a subquery to get those min values by material ID then join to that to get the Supplier ID.
NOTE: This does not handle Suppliers with the same Rate !!!!
SELECT s.SupplierID, s.MaterialID, s.Rate
FROM SupplierRate AS s
INNER JOIN (SELECT MaterialID, MIN(Rate) AS Rate
FROM SupplierRate
WHERE Rate > 0
GROUP BY MaterialID) as minRate
ON minRate.MaterialID = s.MaterialID
AND minRate.Rate = s.Rate
ORDER BY s.MaterialID
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgJuly 20, 2011 at 7:43 am
thanks you sir i got my result !!!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply