Обсуждение: SELECT MAX question

I have an sql tatement in my ASP page that has a WHERE date = (SELECT
MAX(other_date) FROM SomeTable WHERE Blah1 = Blah2) clause.  What's
happening is that the query is grabbing the latest date ONLY if something
something is in the "other_date" field.  If the "other_date" field is blank,
then the record is not chosen.  My question is, how do I grab the latest
date of "other_date" even if the it happens to be blank?



______________________________________________________________________
Posted Via Uncensored-News.Com - Still Only $9.95 - http://www.uncensored-news.com
   With Seven Servers In California And Texas - The Worlds Uncensored News Source

On Sat, Mar 31, 2001 at 09:11:39PM -0500, Jacob wrote:
> I have an sql tatement in my ASP page that has a WHERE date = (SELECT
> MAX(other_date) FROM SomeTable WHERE Blah1 = Blah2) clause.  What's
> happening is that the query is grabbing the latest date ONLY if
> something something is in the "other_date" field.  If the "other_date"
> field is blank, then the record is not chosen.  My question is, how do
> I grab the latest date of "other_date" even if the it happens to be
> blank?

Think about what you're asking.  How can anything or anybody know
whether or not a NULL date is greater than some other date?  Maybe you
want to look into having a timestamp with a default of
CURRENT_TIMESTAMP?

--
Eric G. Miller <egm2@jps.net>

Jacob wrote:

> I have an sql tatement in my ASP page that has a WHERE date = (SELECT
> MAX(other_date) FROM SomeTable WHERE Blah1 = Blah2) clause.  What's
> happening is that the query is grabbing the latest date ONLY if something
> something is in the "other_date" field.  If the "other_date" field is blank,
> then the record is not chosen.  My question is, how do I grab the latest
> date of "other_date" even if the it happens to be blank?

    Adding my own question to this, does pgsql have an equivalent to the
Oracle NVL function?



--
  _
/ \   Leandro Guimarães Faria Corsetti Dutra         +55 (11) 3040 8913
\ /   Amdocs at Bell Canada                          +1 (514) 786 87 47
  X    Support Center, São Paulo, Brazil          mailto:adbaamd@bell.ca
/ \   http://terravista.pt./Enseada/1989/    mailto:leandrod@amdocs.com

Yes. Its COALESCE(). Example:

SELECT COALESCE(NULL, 'Mike');
  case
________
 Mike
(1 row)

Hope that helps,

Mike Mascari
mascarm@mascari.com

-----Original Message-----
From:    ADBAAMD [SMTP:adba.amdocs@bell.ca]
Sent:    Sunday, April 01, 2001 12:33 PM
To:    Jacob
Cc:    pgsql-general@postgresql.org
Subject:    Re: [GENERAL] SELECT MAX question

Jacob wrote:

> I have an sql tatement in my ASP page that has a WHERE date = (SELECT
> MAX(other_date) FROM SomeTable WHERE Blah1 = Blah2) clause.  What's
> happening is that the query is grabbing the latest date ONLY if something
> something is in the "other_date" field.  If the "other_date" field is blank,
> then the record is not chosen.  My question is, how do I grab the latest
> date of "other_date" even if the it happens to be blank?

    Adding my own question to this, does pgsql have an equivalent to the
Oracle NVL function?

>> I have an sql tatement in my ASP page that has a WHERE date = (SELECT
>> MAX(other_date) FROM SomeTable WHERE Blah1 = Blah2) clause.  What's
>> happening is that the query is grabbing the latest date ONLY if something
>> something is in the "other_date" field.  If the "other_date" field is
blank,
>> then the record is not chosen.  My question is, how do I grab the latest
>> date of "other_date" even if the it happens to be blank?
>
> Adding my own question to this, does pgsql have an equivalent to the
>Oracle NVL function?

You might try adding an additional "OR if Blah1 IS NULL" to your WHERE
clause to pickup the NULL values.  This is the way SQL is SUPPOSED to handle
NULL values

len