Top 10 Groups Required ! 1

[alexthomas]

Hi,

I have a reporting issue. In my report, drug details are grouped by Class names. Each drug record has details like prescription count, price etc. The report groups the drugs by their Class. So, under each Class Name, I have the corresponding drug records with summary information. The requirement is to display only the top 10 Classes based on the summary information. The summary info. could be the total number of prescriptions for all drugs under the particular Class. Any thoughts?

Thanks
Alex

 

[lupins46]

I'm not convinced this is the best way but you cannuse something like:


select * from mytable inner join
(select top 10 q1.class from
(Select class, count(prescriptionid)as Knt from
mytable group by class)as q1
order by q1.knt desc)as q2
on mytable.class = q2.class

Then in the report you would group by class.

 

[alexthomas]

Hi lupins46,

Your query and suggestion helped me to write a query that fetched the correct results ! Thanks a lot. I used the following query:

SELECT tblROROutput.*
FROM tblROROutput INNER JOIN [SELECT TOP 10 tblROROutput.HIC3
FROM tblROROutput
GROUP BY tblROROutput.HIC3,tblROROutput.REBATE_RX_COUNT_PER_CLASS
ORDER BY tblROROutput.REBATE_RX_COUNT_PER_CLASS DESC]. AS q2 ON tblROROutput.HIC3 = q2.HIC3;

Regards,
Alex