Home > Web > PHP >

MySQL Sort Order with NULL

Posted Jul 11, 2004
Last Updated Aug 17, 2010
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.

You can also shorten this up by using the ISNULL() function. The ISNULL() function returns 1 if the parameter is null, and 0 otherwise. The query now looks like: SELECT * FROM people ORDER BY ISNULL(nickname), nickname ASC; It will return:

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

(Thanks to Amit Prasad for pointing out this cleaner solution.)

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

Comment

No HTML Tags are permitted.

tylmaster

Sep 14, 2012

You made my day, thanks a lot!
(Should have known this years before...)

Nico van de Kamp

Sep 12, 2012

I have found here my inspiration but somewhere else my solution.
This works so far, if the value is NULL. But if the value is empty '', then still problems occurs or the sorting is not working. Therefore you can use:

select * from table
order by if (field = '' or field is null,1,0), field

or:

IF(l_name IS NULL or l_name='', 1, 0) AS isnull
FROM students
ORDER BY isnull ASC, l_name ASC;";

steven

Sep 4, 2012

This is what I've been googling for hours. Thanks!

Rohit

Aug 10, 2012

Thanx a lot for this code .... :D

Daniel

Jun 12, 2012

Great!

barry

Mar 25, 2012

brilliant! so easy to find and simple to implement when i ran into this same thing.

TheGreatGonzo

Mar 6, 2012

great tip, the latter condensed version accomplished exactly what I was looking for

Blacksheep

Feb 23, 2012

Thank you!

Zeeshan A Zakaria

Jan 25, 2012

Thanks for this solution. I was wondering how to do it, facing the exact same situation which you described, where NULL rows should be below the rows with values. I never thought that I would find a solution on Google so quickly, and was looking forward to spend hours to figure this out. So thanks a lot for saving my time.

Tony

Jan 11, 2012

Thank you! EXACTLY what I was looking for!

Gerardo

Dec 28, 2011

Thank you! Very helpful!

sky

Jun 13, 2011

Thank you! It's work!

Harry Groover

Apr 13, 2011

So very helpful, thank you!

John Smith

Mar 24, 2011

Cheers great help ;)

Joel Harris

Feb 24, 2011

Thanks for the pointer

joe mama

Nov 29, 2010

takes too long with large data sets

Ani

Sep 29, 2010

Hi Andrew,

Thanks for the article.

This is exactly what I was looking for.

:)

Amit Prasad

Jul 20, 2010

I was looking for a solution to this problem and this is the answer what I wanted. I modified this query a bit so that I do not need to append a new column to my resultset.



SELECT * FROM people ORDER BY ISNULL(nickname), nickname;



Thanks Andrew.

Doug

Mar 1, 2011

Thanks Andrew and Amit -- This was just what I was looking for as a way to push empty values to the end of the list. And Amit's simpler solution was perfect!
Sky Writer