RE: Simple SQL Question

From: Raj Gopalan <raj.gopalan_at_netdecisions.co.uk>
Date: Mon, 20 Nov 2000 19:57:42 -0000
Message-Id: <10686.122536@fatcity.com>

select *
  from inpatient in1
 where admdatetime = (select max(admdatetime)

                        from inpatient in2
                       where in2.icons = in1.icons);

-----Original Message-----
From: Peter Hazelton [mailto:peterhazelton_at_hotmail.com] Sent: Monday, November 20, 2000 4:21 PM
To: Multiple recipients of list ORACLE-L Subject: Simple SQL Question

Hi Everyone

Need a little help here if you can.

Consider this query returning the following:

select     icons,
           max(admdatetime),
           admdatetime
from       inpatient
where      icons < 20

group by admdatetime, icons
/

ICONS MAX(ADMDA ADMDATETI

---- --------- ---------
   4 09-JAN-99 09-JAN-99
   4 22-FEB-99 22-FEB-99
   5 19-DEC-98 19-DEC-98
   7 12-JUN-98 12-JUN-98
   9 23-SEP-98 23-SEP-98
  10 10-AUG-99 10-AUG-99
  10 08-NOV-99 08-NOV-99
  11 12-DEC-97 12-DEC-97
  11 28-JAN-98 28-JAN-98
  14 20-MAY-98 20-MAY-98
  17 19-OCT-97 19-OCt-97

What I want to have happen is just return the rows where the admission date is the greatest and ignore the rest (For example, I just want to see the following.)

ICONS MAX(ADMDA ADMDATETI

---- --------- ---------
   4 22-FEB-99 22-FEB-99
   5 19-DEC-98 19-DEC-98
   7 12-JUN-98 12-JUN-98
   9 23-SEP-98 23-SEP-98
  10 08-NOV-99 08-NOV-99
  11 28-JAN-98 28-JAN-98
  14 20-MAY-98 20-MAY-98
  17 19-OCT-97 19-OCt-97

The query the way it is will not work because I have admdatetime included as

well. If I take out admdatetime it only returns the one row but the problem is I need all of the information contained in the row, not just the ICONS and Max(admdatetime).

Is this an easy one and I am just missing something or what?

Thanks for your help and please email me if the question is not clear.

---

Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at http://profiles.msn.com.

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Peter Hazelton
  INET: peterhazelton_at_hotmail.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may Received on Mon Nov 20 2000 - 13:57:42 CST