Grouping and TOP 10

I have a table (medical data relating to the number of patients admitted into a ward with different illnesses) which has 3 columns I am interested in.

Column 1 is Ward,
Column 2 is the diagnosis,
Column 3 is the nuber of patients admitted into the ward with the diagnosis.

Firstly, i know this data is not relational (it is a warehouse and we only have flat files) which i think might be what is causing my problems.

What i want to do is write a query that will give me the top 10 for each ward based on the number of patient admitted

eg.

Ward Diagnosis Number of Patients
######################################
1 Broken Leg 107
1 Broken Hip 98
1 Broken Nose 56
...
2 Lung Cancer 105
2 Liver Cancer 65
...
etc

Does anybody know how to do this as i keep going round in circles and i can't work out how to do it.

Thanks in advance,

Emma.Which DBMS? Some (e.g. Oracle) have "analytic functions" that make this sort of query easy. Without analytics, you could do something like:

select ward, diagnosis, num_patients
from table t1
where 10 > (select count(*) from table t2 where t1.ward = t2.ward and t2.num_patients > t1.num_patients);|||SQL Server back-end but i am running the query in an access front end (so either would be fine as i can put in in the front of back)|||and i worked it out from a post on the SQL Server board:

SELECT *
FROM tab AS a
WHERE ((a.num_patients) In (select top 3 num_patients from tab b where a.ward = b.ward))
ORDER BY a.Ward, a.num_patients DESC;

Thanks for the help.sql