Обсуждение: 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