SQL - greates value based on 2 columns

From: wysza <awysza_at_yahoo.com>
Date: Sun, 07 Jan 2001 21:21:59 GMT
Message-ID: <X5566.4170$bR3.1217879@typhoon2.ba-dsg.net>

Table:
desc therapies

id number                  ---  unique key
patient_id number
th_seq_no number     --- this sequence number is per_patient: each patient
has his/her own sequence
th_date number         ----of therapy

status number
desc varchar2

I need to select last therpy done on each patient (most recent date). The probelm is : if a patient had 2 therapies during one day (column: th_date)
i should choose this therapy, which has greater th_seq_no. How can i do this?

I tried this query:
select * from therapies
where (patient_id, th_date) in (select patient_id, max(th_date) from therapies
group by patient_id);

But it doesn't guarantee that only one row per patient will be returned from the query.
ex.:

       id patient_id status desc th_seq_no th_date

---------- ---------- --   ---------- --------- ---------
       210       7369    0        3010          3   10-OCT-86
       211       7369    1        3011          2   10-OCT-86
       209       7499    1        3009          1   09-SEP-87
       201       7521    1        3001          1   01-JAN-95
       115       7566    0          315          1   15-MAR-99
       203       7698    1        3003          1   03-MAR-93
       204       7782    0        3004          0   04-APR-92
       215       7782    1        3015          1   04-APR-92

I need sth that gives me greatest value based on two fileds not just one, but I don't know how to do it in SQL....

wysza Received on Sun Jan 07 2001 - 15:21:59 CST