ORDER BY if the column is empty?

[Subjective Effe]

The result of my table has some empty cells where there is no data in that particular column for the item.

But where there is data it is numerical and I want to order it. The problem is ASC and DESC both place the items with empy fields at the top! I would rather they were always shoved to the bottom.

Is there a way to do this?

[afterburn]

MS-SQL

select * from tbl order by IsNull(feild,100000000)

[Subjective Effe]

Can you explain that to me?

Also, I forgot to mention, I'm using php/mysql.

[Subjective Effe]

. . .and the column is derived from AVE. . . ;)

[scoutt]

in mysql you have "is null" and "is not null" but I am not sure if you can use them in the order by clause.
just don't order by AVE.

[Subjective Effe]

I have to order by AVE. I have found a solution. Here it is:

SELECT ...
, Avg(ratings.rating) AS Average_Rating
,COALESCE(Avg(ratings.rating)
/ Avg(ratings.rating),-1) as Sortkey
FROM ...
GROUP
BY ...
ORDER
BY sortkey desc
, Average_Rating desc


Dividing Average_Rating by itself will give 1 if it is not empty but NULL is if it is, and thus Sortkey becomes -1. You then sort by Sortkey (either 1 or -1) and then by the actual Average_Rating.

For those that happen upon this thread and are unaware of the COALESCE function in mySQL: it was introduced in v3.23.3 and takes the first non-null value from the variables passed to it thus: COALESCE(x,y,z)