This discussion is archived
3 Replies Latest reply: Nov 22, 2012 9:19 PM by sukhijank RSS

window query where there are multiple maximum values

869931 Newbie
Currently Being Moderated
Hi All,

I have the following scenario:

A patient can have a fever (temperature over normal) for a certain number of days.

They may have a fever many times.

e.g. 1st Jan - 10th Jan, maxmim temp was 40C
1st Feb - 18th Feb, max temp was 40.3C
1st March - 3rd march, max temp was 39.9C

There is no identifier in the table which uniquely identifies periods of fever (e..g first time the patient has been ill, second time the patient has been ill, etc.).

However there is a field which identifies the day of the fever. So for the example above:
1st Jan - 10th Jan: There will be 10 records with DAY going from 1 to 10
etc.

How can I write a query that will return one record for each time the patient was ill and the maximum temperature the patient had in that bout of illness?

Thanks.

PC :-)
  • 1. Re: window query where there are multiple maximum values
    812975 Explorer
    Currently Being Moderated
    can you please provide more sample data and more clear scenario ?
  • 2. Re: window query where there are multiple maximum values
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    ec wrote:
    ... How can I write a query that will return one record for each time the patient was ill and the maximum temperature the patient had in that bout of illness?
    That depends on exactly what your table is like.

    Whenever you have a question, please post a little sample data (CREATE TABLE and INSERT statements) for all the tables involved, so the people who want to help you can re-create the problem and test their ideas. Also post the results you want from that data, and an explanation of how you get those results from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say what version of Oracle you're using (e.g. 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

    From what you've said so far, it sounds like you'll need to group rows, so that all the rows for the same period of fever have something in common, similar to the grouping in {message:id=9953384}
    Once you've assigned group id's, getting the highest temperature in each group is just a matter of using the aggregate MAX function.
  • 3. Re: window query where there are multiple maximum values
    sukhijank Explorer
    Currently Being Moderated
    Hi,

    May be you are looking for something like this:
    create table patient_fever_record
    (
    patient_id   NUMBER,
    fever_date DATE,
    max_temp NUMBER
    );
    
    begin
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(1, to_date('01-01-2012', 'DD-MM-YYYY'), 39.0);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(1, to_date('02-01-2012', 'DD-MM-YYYY'), 39.3);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(1, to_date('07-01-2012', 'DD-MM-YYYY'), 40.1);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(1, to_date('08-01-2012', 'DD-MM-YYYY'), 38.7);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(1, to_date('09-01-2012', 'DD-MM-YYYY'), 37.9);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('01-01-2012', 'DD-MM-YYYY'), 40.4);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('03-01-2012', 'DD-MM-YYYY'), 39.5);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('05-01-2012', 'DD-MM-YYYY'), 39.5);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('06-01-2012', 'DD-MM-YYYY'), 39.5);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('11-01-2012', 'DD-MM-YYYY'), 40.2);
    insert into patient_fever_record(PATIENT_ID, FEVER_DATE, MAX_TEMP) values(2, to_date('12-01-2012', 'DD-MM-YYYY'), 40.3);
    COMMIT;
    END;
    
    SELECT   patient_id,
             MIN (fever_date) startdt,
             MAX (fever_date) enddt,
             MAX (max_temp) max_temp
    FROM     (SELECT patient_id,
                     fever_date,
                     fever_date - ROW_NUMBER () OVER (PARTITION BY patient_id ORDER BY fever_date) grp,
                     max_temp
              FROM   patient_fever_record)
    GROUP BY patient_id, grp
    ORDER BY patient_id, startdt;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points