Dennis,
Thanks for the timely reply.
max(coalesce(col1, 0), coalesce(col2, 0))
is a lot cleaner than the
max(
case when col1 is null then 0 else col1 end,
case when col2 is null then 0 else col2 end
)
solution I came up with. Though the performance seems to be about the same.
The "coalesce" word hasn't been in my vocabulary, so it has no meaning for me.
I'll have to read-up :-))
-Clark
----- Original Message ----
From: Dennis Cote <[EMAIL PROTECTED]>
To: sqlite-users@sqlite.org
Sent: Wednesday, January 31, 2007 11:40:02 AM
Subject: Re: [sqlite] NULL always greater?
Clark Christensen wrote:
> I've read through numerous discussions here about comparing values with null,
> and how SQLite functions work with null values, and I thought I understood.
>
> Now it seems appropriate to use the max(col1, col2) function to find the
> latest of two dates (integer Unix times), and some rows will contain null in
> one column or the other. But, max() always returns null when one of its args
> is null. That just seems backwards :-))
>
> FWIW, I'm on 3.3.12 on both Windows and Linux.
>
> Any help is appreciated.
>
>
Clark,
You must reassign the value used for the comparison if it is null.
The coalesce function will return the first non null value in its
arguments, and this may be all you need. If you only want the value from
col2 if col1 is null then simply use
coalesce(col1, col2)
You will only get a null result if both columns are null.
If you really wan the max of the two columns you can use coalesc to
convert nulls into zeros for the max function.
max(coalesce(col1, 0), coalesce(col2, 0))
This will give a result of zero if both columns are null.
HTH
Dennis Cote
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
To unsubscribe, send email to [EMAIL PROTECTED]
-----------------------------------------------------------------------------
