Top 5 per multiple columns

[tmcrouse]

I have a database that I just imported a table and I am trying to get the top 5 by desc order of my member count. The problem is some of the counts are duplicated as 1 but the other columns are not a duplicate. I am attaching an example of the Excel I am importing, but this is just an example. I have multiple product across all states and products and then there is WLP, AGP, MMP under Legacy. Then I have the top 10 top1dx and the ranking is actually 1 to 10. The layout was pulled asking for the top10 diagnosis or dx1 and then give me all the dx2's that fall under that dx1. So I might have 100 dx2's but only need the top 5 but as I said with the count of 1 on many, if I do my query, it will give me the top 5 and chop off any 1's but then I don't get each ranking 1 through 10. I will post an example also of what I need for the output and just having a difficult time with the right query. Here is the query I used:

SELECT OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT,  OP.ADMITQTY, OP.STATE, OP.PRODUCT
FROM OP
WHERE ((([OP].[TOPRANK]) In (SELECT TOP 5 TR.TOPRANK
FROM OP as TR
where OP.TOPRANK = TR.TOPRANK
ORDER BY TR.MEMBERCOUNT DESC)) AND (([IP].[MEMBERCOUNT])>1))
GROUP BY OP.CM_ID, OP.LEGACY, OP.TOPRANK, OP.MEMBERCOUNT, OP.ADMITQTY, OP.STATE, OP.PRODUCT
ORDER BY OP.MEMBERCOUNT DESC;

Even if the top 5 ends up showing only 3 in the mix, that is fine. As long as I have in the end every legacy, product, state, the dx1, dx2, toprank accounted for by memebecnt desc. I hope this example helps.

 

[jdraw]

Similar post at
http://www.accessforums.net/queries/top-5-per-several-conditions-50933.html

I suggest you break the problem into smaller pieces and get 1 of those pieces working. Then start to add the other pieces.

If there are only 3 records in a given group, and you are looking for top 5, you will only get 3. There just aren't 5 to select.

The go to reference for top N is the allen browne link you were given in the other forum.

Good luck.