Here is an overview of what I am doing. I have a table with around 73,000 records in it. There are around 200 different MeasureID's with an associated Rate. I have to get the top ten Rates for each MeasureID. Each MeasureID has around 300 records associated with it.
With the help of several people in this forum (John V and Bob H come to mind)I was able to get the sorting/ranking to work. The problem is, it takes more than 30 minutes to run the query. Is there a way to modify the below query to speed the process up? I thought about doing some kind of sort to get the records for each MeasureID down to maybe 30 records then do the ranking but I'm not sure how to build the initial sort.
SELECT M.MeasureID, M.Rate, M.subtblHEDISMeasureID_ShortName, M.OrgID INTO tblTopTenFinalResults
FROM tblTopTen AS M
WHERE (((M.Rate) In (select top 10 X.Rate
from [tblTopTen] as X
where (X.[MeasureID]=M.[MeasureID])
and (IsNumeric(X.Rate)<>0)
order by Val(X.Rate) desc)))
ORDER BY M.MeasureID, M.Rate DESC;
Thanks, John F.
Tidak ada komentar:
Posting Komentar