Home > Web > PHP >

MySQL Sort Order with NULL

Andrew Penry

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.

Matt Thomas

Mar 20, 2017

Thank a lot for the code. You saved my day!

Reply

tylmaster

Sep 14, 2012

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

Reply

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;";

Reply

steven

Sep 4, 2012

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

Reply

Rohit

Aug 10, 2012

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

Reply

Daniel

Jun 12, 2012

Great!

Reply

barry

Mar 25, 2012

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

Reply

TheGreatGonzo

Mar 6, 2012

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

Reply

Blacksheep

Feb 23, 2012

Thank you!

Reply

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.

Reply

Tony

Jan 12, 2012

Thank you! EXACTLY what I was looking for!

Reply

Gerardo

Dec 28, 2011

Thank you! Very helpful!

Reply

sky

Jun 14, 2011

Thank you! It's work!

Reply

Harry Groover

Apr 13, 2011

So very helpful, thank you!

Reply

John Smith

Mar 24, 2011

Cheers great help ;)

Reply

Joel Harris

Feb 24, 2011

Thanks for the pointer

Reply

joe mama

Nov 29, 2010

takes too long with large data sets

Reply

Ani

Sep 29, 2010

Hi Andrew,

Thanks for the article.

This is exactly what I was looking for.

:)

Reply

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.

Reply

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!

Reply

Wall Worm plugins and scripts for 3ds Max