Grouping and TOP 10

emma_com · #1 (**permalink**) 02-24-04, 06:59

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.

andrewst · #2 (**permalink**) 02-24-04, 07:06

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);

emma_com · #3 (**permalink**) 02-24-04, 07:10

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)

emma_com · #4 (**permalink**) 02-24-04, 08:09

and i worked it out from a post on the SQL Server board:

Code:

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech