ORDER BY if the column is empty?

[Subjective Effec]

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?

[worchyld]

Is it not possible to perform the following:

...your SQL code...
WHERE column <> ""
ORDER BY column ASC

[Subjective Effec]

I cannot append that to the end of the query, it doesn't work.

The query is quite complex and integrating that is not an option. I need something that I can just add to the ORDER BY section.

[r937]

ascending:

order 
    by case when columnx is null then 1 else 0 end
     , columnx asc

descending:

order 
    by case when columnx is null then 1 else 0 end
     , columnx desc

[Subjective Effec]

That doesn't seem to work because columnx is an average, e.i. is Average_Num from "Avg(table.varNum) AS Average_Num".

[r937]

can't help you if i can't see the query

[Subjective Effec]

I've sent it to you.

[r937]

SELECT ...
     , Avg(ratings.rating) AS Average_Rating     
  FROM ...
GROUP 
    BY ...
ORDER 
    BY CASE WHEN Average_Rating IS NULL THEN 1 ELSE 0 END
     , Average_Rating ASC

if this doesn't work, there must be some other reason

i think

[Subjective Effec]

This is the error I get:

Error performing query: Unknown column 'Average_Rating' in 'order clause'

The thing is if I just order it by Average_Rating DESC or ASC it works, accepting Average_Rating as a column. But as it can be empty this is not a good way to do it.

I could make sure that there is always a rating but practically that would be very difficult, and I need the backup just in case. . .

[r937]

phenomenal

you're right, i can't get it to work either

and i've been all over the docs

oh well, time for a hack

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 ASC

[Subjective Effec]

Well this has certainly been the runaround hasn't it?

But it works now! And frankly, I'd expect no less from you Rudy, you always come up with the goods. Thanks yet again. I think I'm going to have to link your site from mine and put you in the credits! Somehow I think that isn't the first time I'm said that.

Now I'm off to research COALESCE, I have to know how my own site works after all.

Why do you call it a hack though?

edit [post-research]

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

What version of mySQL had COALESCE introduced? I can't see in the mySQL docs. At home I have 3.23.49, my host (where the site is destined to end up) uses 3.23.24. Well that's what they say on the site. phpinfo tells me they have 3.23.39. It's still a lower version than mine.

[r937]

thanks for the kind words

the docs say that COALESCE came in with 3.23.3

you could use IF(foo is null, -1, foo)

i think