Re: [sqlite] NULL always greater?
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] -
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] -
Re: [sqlite] NULL always greater?
Aah, perfect. Thanks for the pointer. -Clark - Original Message From: Dan Kennedy <[EMAIL PROTECTED]> To: sqlite-users@sqlite.org Sent: Tuesday, January 30, 2007 10:49:34 PM Subject: Re: [sqlite] NULL always greater? The basic rule is that the result of any comparison involving a NULL value (including comparing against another NULL) is NULL. See the following for the details: http://www.sqlite.org/nulls.html Dan. On Tue, 2007-01-30 at 16:41 -0800, 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. > > Thanks! > > -Clark > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] NULL always greater?
The basic rule is that the result of any comparison involving a NULL value (including comparing against another NULL) is NULL. See the following for the details: http://www.sqlite.org/nulls.html Dan. On Tue, 2007-01-30 at 16:41 -0800, 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. > > Thanks! > > -Clark > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] NULL always greater?
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. Thanks! -Clark - To unsubscribe, send email to [EMAIL PROTECTED] -
