Top 5 per several conditions

[tmcrouse]

I know how to query the top 5 in descending order by a count of something but not sure how to apply it across several parameters.



I can sort my membercount by descending. But I need the top five membercounts by descending per each state, product, line of business and diagnosis I have. My table has the top 100 diagnosis per state and there are 21 states. Each state has an HMO and PPO. So that means CA would be 200 rows because we have top 100 per HMO and PPO. Then I have to throw the line of business which is govt business and commercial business. So this means CA will appear 4 times in the database with 400 total rows. Each of the top 100 diagnosis could be different. So in CA HMO Commercial you might have hypertension as the top by membercount descending. Then in CA PPO Commercial is might be breast cancer. I am looking to obtain the top 5 per each state, product, line of business and diagnosis by descending membercount. does this even make sense?

[orange]

There is a sample to get started at Top N query

[tmcrouse]

Well I tried it like this and it just pasted all original 13925 records into a new table. I tried using the primary key as my join. Then the second code I am trying by taking toprank and = to toprank and that is still running.

SELECT IP.cm_id, IP.ipop, IP.legacy, IP.toprank, IP.topdx1, IP.topdx1desc, IP.dx2, IP.dx2desc, IP.membercount, IP.admitqty, IP.state, IP.product INTO a
FROM IP
WHERE (((IP.[toprank]) In (SELECT TOP 5 tr.toprank
FROM ip as tr
where tr.cm_id = ip.cm_id
ORDER BY tr.membercount DESC)))
GROUP BY IP.cm_id, IP.ipop, IP.legacy, IP.toprank, IP.topdx1, IP.topdx1desc, IP.dx2, IP.dx2desc, IP.membercount, IP.admitqty, IP.state, IP.product
ORDER BY IP.membercount DESC;

SELECT IP.cm_id, IP.ipop, IP.legacy, IP.toprank, IP.topdx1, IP.topdx1desc, IP.dx2, IP.dx2desc, IP.membercount, IP.admitqty, IP.state, IP.product INTO a
FROM IP
WHERE (((IP.[toprank]) In (SELECT TOP 5 tr.toprank
FROM ip as tr
where tr.toprank = ip.toprank
ORDER BY tr.membercount DESC)))
GROUP BY IP.cm_id, IP.ipop, IP.legacy, IP.toprank, IP.topdx1, IP.topdx1desc, IP.dx2, IP.dx2desc, IP.membercount, IP.admitqty, IP.state, IP.product
ORDER BY IP.membercount DESC;

[orange]

Ok Here's an often referenced link Allen Browne Top N See the TOP N heading.

[tmcrouse]

It's not working. If I do the query without the membercount >1 it just returns the same rows. Putting in the membercount >1 returns the top dx2, and the top rank however it leaves some ranks off like for example rank 7. When I go back to original data, it is because rank 7 has all 1's for the membercount. I am trying to figure out a way to code this so it will say give me the top 5 of the dx2 and all the topranks which is 1 through 10 and all the other related fields. I tried breaking it up and doing membercount >1 and then membercount =1 in another table and then manually going in and seeing oh rank 7 is missing, copy and paste missing but it was a nightmare. I am just not sure what to do at this point. Either I VBA code the original Excels or manually go through 1 by 1. 1 Excel has 14950 rows, the other 97203. I have spun my wheels, chased my tail like a dog for over 24 hours trying to get this to work and told my mgr this is nuts. Over the last 24 hours I could have manually done it and been done with it. I just know there has to be a way using code or doing it programmatically.