Re: SQL - greates value based on 2 columns

mmm,

officially you could

where
(column1, column2) = (select max(), max() so combine the two subqueries into one.
I'm not sure though whether the results are going to be identical.

Hth,

Sybrand Bakker, Oracle DBA

"wysza" <awysza_at_yahoo.com> wrote in message news:QIi66.6537$8O3.1421981_at_typhoon2.ba-dsg.net...
> > 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 - 12:01:34 CST