Re: using greatest function

How about...

select max(greatest(opendate,sdate))
from site s, cust c
where s.cid.cid=c.cid

However, be careful since opendate is nullable and the oracle functions return null when an input parameter is null. Maybe...

select max(greatest(nvl(opendate,sdate),sdate)) from site s, cust c
where s.cid.cid=c.cid

Richard

Rolf wrote:
>
> Hi:
> I have to find the most recent date from 2 tables:
>
> table cust:
> CID NOT NULL NUMBER(8)
> OPENDATE DATE
>
> and table site:
> SID NOT NULL NUMBER(8)
> CID NOT NULL REF OF CUST_t
> SDATE NOT NULL DATE
>
> A cust may have 1 or more site records. I need to find the most
> recent OPENDATE or the most recent SDATE, which ever is more recent.
> For example:
> CID OPENDATE SID SDATE
> 1141 09-MAR-2002 101 09-MAR-2002
> 1141 09-MAR-2002 102 11-MAR-2002
> 1141 09-MAR-2002 103 01-APR-2002
>
> Should chose 01-APR-2002, since it is the most recent of all the
> SDATEs and the cust's OPENDATE
>
> I have:
> select greatest(opendate,max(sdate)) from site s,cust c where
> s.cid.cid=c.cid
> but SQL says "not a single group-by function"
> Any help is appreciated.
> - Rolf.
  Received on Wed Apr 24 2002 - 00:12:14 CEST