"Top 10" query

How do I do this in SQL?
I have a table I'm querying with the following fields (simplified
here):
facility_name char(15), drug_product char(64), total_cost money
How can I produce the following in SQL so my result can answer the
question
What are the top 10 drugs dispensed by facility_id when looking at
total_cost?
output should look like this:
facility_id drug_product total_cost
houston Simvastatin 500
houston Sertraline 400
...
next 8 for houston
...
chicago Epoetin 1000
chicago Atorvastatin 800
...
next 8 for chicago
...
etc.
I'm thinking this has to done in more than one statement since TOP will
not work here (as far as I can see) I miss the sequential record
"file" environment of foxpro/access here. I can do this in either of
those 2 apps with about 1 min of coding but I want to learn how to do
this in a SQL tbl.Without DDLs, here is a skeleton query:
SELECT * -- use column names
FROM tbl t1
WHERE ( SELECT COUNT( * )
FROM tbl t2
WHERE t2.facility_id = t1.facility_id
AND t2.total_cost >= t1.total_cost ) <= 10 ;
You have not specified how you'd want to resolve the ties. If ties are
involved, change the correlation like:
WHERE t2.facility_id = t1.facility_id
AND ( t2.drug_product = t1.drug_product
AND t2.total_cost >= t1.total_cost )
OR t2.total_cost >= t1.total_cost )
In t-SQL you can easily use a TOP clause with ORDER BY to achieve similar
results. You may also want to refer to www.aspfaq.com/2120 for some similar
ideas
Anith|||Supposing there are no ties, then:
select a.*
from t as a
where (select count(*) from t as b where b.facility_name = a.facility_name
and b.total_cost <= a.total_cost) <= 10
AMB
"mwrobe" wrote:

> How do I do this in SQL?
> I have a table I'm querying with the following fields (simplified
> here):
> facility_name char(15), drug_product char(64), total_cost money
> How can I produce the following in SQL so my result can answer the
> question
> What are the top 10 drugs dispensed by facility_id when looking at
> total_cost?
> output should look like this:
> facility_id drug_product total_cost
> houston Simvastatin 500
> houston Sertraline 400
> ...
> next 8 for houston
> ...
> chicago Epoetin 1000
> chicago Atorvastatin 800
> ...
> next 8 for chicago
> ...
> etc.
> I'm thinking this has to done in more than one statement since TOP will
> not work here (as far as I can see) I miss the sequential record
> "file" environment of foxpro/access here. I can do this in either of
> those 2 apps with about 1 min of coding but I want to learn how to do
> this in a SQL tbl.
>|||Thank you both gentlemen. It looks like I need to read up and
experiment more on subqueries. Thanks for the link as well.