SQL order by only for non empty fields

[ofekhorizon]

Hi there,

I have a dating website.

When a user searches for members, I would like to display first, the ones with the photos (shuffled) and then the others (shuffled as well).

-I can't use ---WHERE(img <> '')--- because then it won't get the ones without the photo.

-If I use ORDER BY, it put them always in the same order, according to the name of the the photo.

How do I solve this one??

Thanx in advance,
Ofek

 

[Administrator]

Can you use IS NOT NULL in your SQL Statement?

 

[cjard]

Hi there,

I have a dating website.

When a user searches for members, I would like to display first, the ones with the photos (shuffled) and then the others (shuffled as well).

Erm, okay.. So your ORDER BY is going to look something like:

ORDER BY ISNULL(img, 'zzzzzzzzzzzzzzzzzzzzzz'), NEWID()

if img is an INTEGER or NUMBER field, then you have to ISNULL(img, 99999999999999999)

basically pick a value to replace the NULLs with so that they sort AFTER all the valid values. That might mean using a huge number, or string of characters that sort alphabetically after all the valid ones.. Think on it and have a play

 

[cjard]

Read this too:
http://msdn2.microsoft.com/en-us/library/aa175776(SQL.80).aspx