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: Daniel Morgan <damorgan_at_exesolutions.com>
Date: Tue, 23 Apr 2002 21:17:47 GMT
Message-ID: <3CC5CF6B.A7EC04F2@exesolutions.com>


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.

Because you didn't include the GROUP BY clause in your SQL.

SELECT
FROM
WHERE
GROUP BY This is required by any aggregating function when other fields are included in the query.

Daniel Morgan Received on Tue Apr 23 2002 - 16:17:47 CDT

Original text of this message

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