Getting MySQL to sort NULL values last

zeb

I understand that ORDER BY column_name ASC will sort NULL values before any other values and ORDER BY column_name DESC will sort NULL values after all other values. Is there a way I can get NULL values to sort last without making the rest of the values sort in descending order? In this case I am trying to sort a column of type varchar.

LordShryku

Here's a kind of crap hack that MySQL will happily oblige too....

  SELECT *
    FROM table
ORDER BY ifnull(column_name, 'zzzzzz') ASC

crimaniak

Originally posted by LordShryku
Here's a kind of crap hack that MySQL will happily oblige too....
  SELECT *
    FROM table
ORDER BY ifnull(column_name, 'zzzzzz') ASC
[/B]

No,no,no. Data-dependent way. English is not only language in the world.

Do like this:

ORDER BY column_name IS NULL, column_name

zeb

Originally posted by crimaniak
ORDER BY column_name IS NULL, column_name

Thank you, that works great, but can you explain to me how the expression works? Why doesn't the second occurrence of column_name counter the first?

nemonoman

First sort by whether the column name IS_NULL

IS_NULL returns 1 if column_name is null

So now you've sorted all the nonnulls to the top (zeros)
and all the nulls to the bottom (ones)

Having done this, subsort by column_name
all the nonnull column_names get alpha sorted.

zeb

Ohhh, I get it. Thanks!