Simple SQL Question

From: Peter Hazelton <peterhazelton_at_hotmail.com>
Date: Mon, 20 Nov 2000 16:16:57 GMT
Message-Id: <10686.122508@fatcity.com>

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 Received on Mon Nov 20 2000 - 10:16:57 CST