This article is originally written by :

As a MySQL programmer, I often need to sort lists in alphabetical or numeric order, but many times the columns I use have null values. When ORDER BY col ASC is used, null vales appear at the top of the list; with DESC they appear at the bottom. Often, I would like a list to sort in alphabetical or numeric order, but with NULL at the bottom.
Example: You have a table that stores user information, including nicknames. You users can search this table so that they can find friends on your site. When the sort by nickname, you would like for people without nicknames to be on the bottom of the list.

Try:

   SELECT * FROM people ORDER BY nickname ASC

Doesn’t work, does it? It returns something like:

nickname	name
NULL	Art
NULL	Mary
Abby	Abigal
Bobby	Robert
Cindy	Cynthia

All the NULL values are on top.

What to do? Try this:

 SELECT * , nickname IS NULL AS isnull FROM people ORDER BY isnull ASC, nickname ASC

nickname	name	isnull
Abby	Abigal	0
Bobby	Robert	0
Cindy	Cynthia	0
NULL	Art	1
NULL	Mary	1

What’s Happening?

[nickname IS NULL AS isnull] adds a column named “isnull” and fills it with a 0 if there is a value for nickname, and 1 if nickname is NULL. We sort first on this “isnull” column, which puts nicknames first, and null values last. We then sort alphabetically by nickname.

Knowledge is power. Power corrupts. You are now more corrupted.

PS
If you have null values and empty strings you wish to force to the end you will need to use the IF function:

SELECT * , IF(nickname IS NULL or nickname='', 1, 0) AS isnull FROM people ORDER BY isnull ASC, nickname ASC

Related Articles

• February 28, 2008 -- Creating Custom Error Messages in Apache
• February 21, 2008 -- Unicode Data with PHP 5 and MySQL 4.1

Tags: mysql, NULL, ORDER BY