Re: display top 5 records of each subgroup in a query




SELECT T.*
FROM TheTable as T
WHERE T.[Brand Name] IN
(SELECT TOP 5 T2.[Brand Name]
FROM TheTable as T2
WHERE T2.[General Therapeutic Category] =
T.[General Therapeutic Category]
ORDER BY [# of Rx's] Descending)

Simon L wrote:

I want to set up a separate query to display the top 5 records of each
subgroup. The following are the fields and sample data from another query.
What I want is a query that will display the top 5 Brand Name drugs in
Allergy, in Antiemesis, and in Asthma based on the # of Rxs. Any help will
be greatly appreciated. Thanks

General Therapeutic Category Brand Name # of RX's
ALLERGY ALLEGRA 65
ALLERGY ALLEGRA-D 12 HOUR 41
ALLERGY NASACORT AQ 29
ALLERGY ALAVERT 24
ALLERGY ZYRTEC
23
ALLERGY FLUTICASONE PROPIONATE 20
ALLERGY ZYRTEC-D 10
ALLERGY LORATADINE 10
ALLERGY HYDROXYZINE HCL 7
ALLERGY PROMETHAZINE HCL 7
ALLERGY FEXOFENADINE HCL 5
ANTIEMESIS/ANTIVERTIGO MECLIZINE HCL 31
ANTIEMESIS/ANTIVERTIGO TRANSDERM-SCOP 5
ANTIEMESIS/ANTIVERTIGO PROMETHAZINE HCL 1
ANTIEMESIS/ANTIVERTIGO PROCHLORPERAZINE MALEATE 1
ASTHMA ADVAIR DISKUS 76
ASTHMA ALBUTEROL 56
ASTHMA SINGULAIR 17
ASTHMA COMBIVENT 16
ASTHMA ALBUTEROL SULFATE 15
ASTHMA FLOVENT HFA 9
ASTHMA ATROVENT HFA 9
.