Re: SQL - greates value based on 2 columns

In article <X5566.4170$bR3.1217879_at_typhoon2.ba-dsg.net>,   "wysza" <awysza_at_yahoo.com> wrote:
> 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
>
>

select * from therapies t1
where th_date =

     (select  max(th_date)
      from therapies t2 where t2.patient_id = t1.patient_id
     );

Hth,

--
Sybrand Bakker, Oracle DBA

All standard disclaimers apply
------------------------------------------------------------------------


Sent via Deja.com
http://www.deja.com/