| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.tools -> Re: SQL - greates value based on 2 columns
> (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.
nop. It would work only then, when max(seq) and max(date) were found in the
same record.
I need sth like: select max(date,seq) from .... but it doesn't exist.... :(
it 's not the same, because
the inside query does sth like below:
TABLE tab1
a b c
select max(a), max(b) from tab1 order by c;
2 12 xx
1 24 yy
And the outside query: select * from tab1 where (a,b)=(inside_query) looks for records where a=max(a) AND b=max(b). It'll find it for c='yy' but for c='xx' such record doesn't exist.... so it skips it.
But...
Thank you for all your help. I do appreciate it.
wysza
"Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote in message
news:93cv67$9fo4k$1_at_ID-62141.news.dfncis.de...
> 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 - 18:44:18 CST
 |
 |