Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: using greatest function

Re: using greatest function

From: Antonio Alvarado Hernández <a_alvarado_h_at_terra.es>
Date: Sat, 27 Apr 2002 23:05:10 GMT
Message-ID: <G8Gy8.13283$4Y1.178868@telenews.teleline.es>


You have two choices:

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

Option B:
select max(greatest(s.cid.opendate,s.sdate)) from site s

Antonio

"Rolf" <rolf.kamp_at_verizon.net> escribió en el mensaje news:2963a72.0204231249.78f416d4_at_posting.google.com...
> 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 Sat Apr 27 2002 - 18:05:10 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US