Top 5 by Groupings

  • Thread starter Thread starter js
  • Start date Start date
J

js

Below is my query.
2 tables - Claims Data and Main Member Table

I need to run this query for the top 5 RHNs by count of
TCN (descending) in the Claims Data table by each PhysName
(Main Member table).

What do I change in this query to accomplish this? If I
simply do a Top 5, it does not do it by PhysName

Thanks,
js


SELECT [Claims Data].TCN, [MainMember table].MemberName,
[MainMember table].PhysName AS MostFreqPhys, [MainMember
table].RHN, [Claims Data].DrugName, [Claims
Data].PhysName, [Claims Data].Qty, [Claims Data].SrvDate,
[Claims Data].DaysSupply, [MainMember table].MemberDOB,
[MainMember table].CountofMeds, [MainMember table].Gender,
[Claims Data].PhysID

FROM [Claims Data] INNER JOIN [MainMember table] ON
[Claims Data].RHN = [MainMember table].RHN

WHERE ((([MainMember table].PhysName)=[Provider]) AND
(([MainMember table].CountofMeds)>7));
 
Hi,


SELECT a.*
FROM mytable as a
WHERE a.pk IN ( SELECT TOP 5 b.pk
FROM mytable as b
WHERE a.category=b.category
ORDER BY b.orderbywhat DESC
)
ORDER BY a.category, a.orderbywhat DESC


where pk is the primary key field, category the groups and orderbywhat the
field making the comparison about being the top 5 of what...


Note that "SELECT TOP 5 b.pk" does not mean "take the top 5 pk values", no,
it means "take the top 5 records and from them, keep their pk values". The
syntax may be confusing, at first.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top