Not Null

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

Quote:

quote:Originally posted by planoie
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.

This works in any implementation which does not require that the ORDER BY column be present in the SELECT column list (like SQL Server).

The OP wanted just one from the top 10 list, as I read his post:

Code:

SELECT TOP 1 * FROM (SELECT TOP 10 * ORArticles WHERE COALESCE(Photo1, '') <> '' ORDER BY NewID()) as T1

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

planoie · November 20th, 2003, 05:38 PM

Now that I look at this one more time, I realized that Jeff and I were both missing something...

I think what morpheus might want is a random row out of the top ten of your table based on the MagID, not just 1 random row from all of them. So we must add in the MagID ordering...

SELECT TOP 1 *
FROM (SELECT TOP 10 *
        FROM ORArticles
        WHERE ISNULL(Photo1, '') <> ''
        ORDER by MagID DESC) AS TopTen
ORDER BY NEWID()

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

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

You are quite correct.

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