Re: Is this stuff really SQL 92 compliant

In article <327vr4$dgu_at_pong.lasc.lockheed.com> z50816_at_mip.lasc.lockheed.com (Terry Greenlaw) writes:

>Problem 1)
 

>table T contains:
 

> C1 C2
> -- --
> 1 2
> 2
 

>"select max(c1) from t" returns 1.
>"select max(c2) from t" returns 2.
>"select greatest(c1,c2) from t"
> returns 2 for the first record
> but it returns NULL for the second.
 

>Shouldn't least and greatest have the same functionality on columns that
>min and max do on rows in that you're asking for the greatest or least
>KNOWN value?

But that's not what you're asking for. You're asking for the greatest value, and the value of C1 is unknown. 'NULL' should imvho have been 'unknown' If you want to treat 'unknown' as 'irrelevant', use NVL to lowball it, e.g.

        select greatest(nvl(c1,-1e100),nvl(c2,-1e100)).

Jim Received on Fri Aug 12 1994 - 18:05:43 CEST