Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71

    Unanswered: 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.

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Grouping and TOP 10

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

  3. #3
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    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)

  4. #4
    Join Date
    Mar 2003
    Location
    UK
    Posts
    71
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •