> select * from therapies t1
> where th_date =
> (select max(th_date)
> from therapies t2 where t2.patient_id = t1.patient_id
> );
--
Thanks but..... it wasn't what i needed :( , because there was no filter put
on "th_seq_no" ...
sth like: if there_is_more_than_one_record where th_date=max(date)
for_current_patient_id then
get_this_one_with th_seq_no =max(th_seq_no)
But your answer helped me with solving this problem. :)
Here is the query that works.
btw.: can it be simplified a little?
select * from therapies t1
where th_seq_no=(select max(th_seq_no) from therpies t3
where t3.patient_id=t1.patient_id and
th_date=(select max(th_date) from therapies t2
where t2.patient_id=t1.patient_id))
order by patient_id
results:
id patient_id status desc th_seq_no th_date
--------- ---------- -- ---------- --------- ---------
210 7369 0 3010 3 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
215 7782 1 3015 1 04-APR-92
thanks again.
<sybrandb_at_my-deja.com> wrote in message news:93bp3t$uut$1_at_nnrp1.deja.com...
> 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/
Received on Mon Jan 08 2001 - 06:50:56 CST
