SQL - Selecting by record ranking?

[xrapidx]

I'm having a bad day thinking today. I have a table with records from different sources, I'm trying to select the record with the lowest source ranking for a specific ModelID/MasterValueID combination:

SOURCE|MODELID|MASTERVALUEID|RANKING
1|66612|37|2
1|66612|183|1
3|66612|183|2
3|66612|37|1

So - from the above, I'm trying to get out the records in red... it needs to be fast, and a high record count is expected.

SQL Server 2005

 

[dequadin]

You could use either of these, depending on exactly you want to do:

SELECT  TOP(1) Source, ModelID, MasterValueID, Ranking
FROM tblXrapidx
WHERE ModelID = 66612 and MasterValueID = 37
ORDER BY Ranking
ASC

SELECT ModelID, MasterValueID,  Min(Ranking) AS MinRanking
FROM tblXrapidx
GROUP BY ModelID, MasterValueID

 

[xrapidx]

I'm an idiot - I just realised that it makes no difference, because the MASTERVALUEID is the same for all sources.

PS - Thanks - but don't think either of those would have worked in this case.