Re: Is this stuff really SQL 92 compliant

In article Terry Greenlaw replies:
>In article Jim Hill writes:
>>In article Terry Greenlaw originally wrote:
>>
>>>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)).
>
>My claim is that min() and least() should treat nulls in the same fashion.
>
>min() returns the lowest NON-NULL value. least() should do the same.
>
>Either least() should ignore nulls (my opinion), or min(c1) should return
>null if any row has a null value for c1 (non-compliant SQL). Following your
>suggestion, we would have to use max(nvl(c1,-1e100)) also in case any of the
>rows had nulls.

As someone else pointed out, this is a question that people have been struggling with for awhile. It really is a matter of opinion, IMHO. Oracle SQL does appear to me to be consistent: all group functions (except count(*)) ignore null values -and- all other functions (except NVL) return a null value when operating on any null value. The two exceptions, though inconsistencies, are so for good reason.

But then again, maybe I too easily become an apologist for the status quo. :-)

-- 
============================================================================
Michael Sallwasser  | Down one path is utter dispair and hopelessness. Down 
Northrop Grumman    | the other is total destruction. Let us choose wisely.