Top of category

[showdebola]

Hi guys,

I am trying to run a query that gives me the top names of each category.
This query is getting the data from another query that here i call QrySpreadReport.
Here is the query.

SELECT [S&P Rating].[IdS&Prating], [S&P Rating].[S&P Rating], QrySpreadReport.EntityName, QrySpreadReport.Mid
FROM QrySpreadReport INNER JOIN [S&P Rating] ON QrySpreadReport.[IdS&Prating] = [S&P Rating].[IdS&Prating]
WHERE (((QrySpreadReport.Mid) In (select top 5 Mid from QrySpreadReport where QrySpreadReport.[IdS&Prating] = [S&P Rating].[IdS&Prating] order by Mid desc)))
ORDER BY [S&P Rating].[IdS&Prating] DESC , QrySpreadReport.Mid DESC;


When I run this not only it takes forever but the result is the top 5 records of the entire sample without taking in consideration the categories.

I then tried to transform que sub query QrySpreadReport that I mentioned above into a table and then re run the TOP query above. Interesting enough it worked just fine. I got the TOP 5 names for each category.

Do you guys have any idea why this is happening?
I have to say that the subquery QrySpreadReport makes reference to a huge database.

How should I run this query without having to trasnform the sub query in a table.

Many Thanks