Not Null

morpheus · November 20th, 2003, 03:03 PM

SQLStr="SELECT TOP 10 * FROM ORArticles WHERE "" ORDER by MagID DESC"

after the where statement, how do I choose everything from the column that has an entry. I mean if I want only the articles that have pictures. Can I say where Photo is not null, or something along those lines??

Thanks

planoie · November 20th, 2003, 03:17 PM

That is the exact syntax:

SELECT TOP 10 * FROM ORArticles WHERE Photo IS NOT NULL ORDER by MagID DESC

Peter
------------------------------------------------------
Work smarter, not harder.

morpheus · November 20th, 2003, 03:28 PM

Thanks but it doesn't seem to work. Is it possible that just because a field is empty it's not classified as null? Just a thought because it still just returns the top 10 and not the top ten with pictures

Thanks

planoie · November 20th, 2003, 04:03 PM

In SQL server, any column that allows NULLs can have NULLs. This is not the same as a char field containing "". That is an empty string, but it is not NULL.

Think of it like this... If I asked you for your middle name, but you didn't have one, you could say your middle name is "". That represents something meaningful in the data. It says "this user doesn't have a middle name". If the value were NULL, that would mean "We don't have a value for this field".

"" for strings is kind of like 0 for numbers. They both mean something (or nothing depending on your mood that day. ;)

Try this:
SELECT TOP 10 * FROM ORArticles WHERE ISNULL(Photo, '') <> '' ORDER by MagID DESC

The ISNULL function checks the first argument, if it's NULL it returns the second argument. If it IS NOT NULL then the function returns the first argument.

Peter
------------------------------------------------------
Work smarter, not harder.

Jeff Mason · November 20th, 2003, 04:13 PM

Or, if you are a purist, use the SQL-92 standard COALESCE function, (as opposed to the SQL Server proprietary ISNULL), as:

Code:

SELECT TOP 10 * FROM ORArticles WHERE COALESCE(Photo, '') <> '' ORDER by MagID DESC

I prefer COALESCE because

a. It's SQL-92 standard, and all other things being equal, I prefer to use standard constructs, and

b. COALESCE has marginally more functionality, as you can have several expressions as its arguments, and not just two. The function behaves similarly to ISNULL, returning the first non NULL value in its argument list.

YMMV.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

morpheus · November 20th, 2003, 04:15 PM

That is a thing of Beauty!! You are the man!! lol
Thank you very much

planoie · November 20th, 2003, 04:26 PM

Jeff,

Thanks for that bit of advice. I had not seen the COALESCE function.

For a slightly off topic question, what databases use the TOP keyword? The only place that I recall seeing it is access and ms-sql. Is this not a -92 standard? On that same topic, how would one perform such an operation without the TOP keyword? (I used MySQL once but had issues with not being able to use sprocs and some of the ms-sql specific functionality I got used to.)

Peter
------------------------------------------------------
Work smarter, not harder.

morpheus · November 20th, 2003, 04:31 PM

Here's one for you guys how would you get this
SQLStr="SELECT TOP 10 * FROM ORArticles WHERE ISNULL(Photo1, '') <> '' ORDER by MagID DESC"

To load a different one from the top ten everytime the page loads??

Thanks

planoie · November 20th, 2003, 04:53 PM

I learned this from my buddy Imar just this morning!

Try:
SELECT TOP 10 * FROM ORArticles WHERE ISNULL(Photo1, '') <> '' ORDER NEWID()

This generates a new GUID for every row and orders by that. Because GUIDs are generated in, for all intents and purposes, a random way, it works very well for "randomizing" a set or rows.

Peter
------------------------------------------------------
Work smarter, not harder.

Jeff Mason · November 20th, 2003, 05:02 PM

Quote:

quote:Originally posted by planoie
For a slightly off topic question, what databases use the TOP keyword? The only place that I recall seeing it is access and ms-sql. Is this not a -92 standard? On that same topic, how would one perform such an operation without the TOP keyword? (I used MySQL once but had issues with not being able to use sprocs and some of the ms-sql specific functionality I got used to.)

The TOP keyword is a SQL Server extension to the SQL language, introduced, I think, with version 7. It is not a part of the 92 standard or the 99 standard either (as I recall).

You could use the SET ROWCOUNT function, but, of course, that isn't standard either.

The way to do this sort of thing using only standard SQL is to assign a ranking number to each row, and then select those rows whose ranking number is less than the value you want to restrict by.

In order to assign a ranking number, there needs to a column which orders the rows appropriately. Let's assume the OPs original query and use the MagID value as the ordering value. (I'll forget that this column is probably an Identity value which has no meaning with regard to ranking :D).

The way you assign a ranking number is to use a correlated subquery to partition the table into subsets, where each subset contains all of the rows with an ordering value less than the 'current row'. The ranking for the current row is then the COUNT of the number of elements in that subset:

Code:

SELECT T0.Photo, ... ,
    (SELECT COUNT(DISTINCT MagID) FROM ORArticles T1
        WHERE T1.MagID<=T0.MagID) AS Ranking
    FROM ORArticles T0
    WHERE Ranking <= 10;

Note that the DISTINCT MagID isn't really necessary if MagID really is an identity value (unique), but is necessary in the general case where the ranking column might be sales figures or something like that and where ties are possible.

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com