MySQL: How to sort by column in ascending order, and show NULL at the end instead of the beginning?

Question

I know how to sort a result set:

SELECT * FROM `People` ORDER BY `LastName` ASC

However, the results that have an empty LastName show at the beginning. How do I sort in ascending order, and show the NULL results at the end instead of the beginning?

Answer 1

SELECT * 
FROM `People` 
ORDER BY case when `LastName` is null then 1 else 0 end, 
    `LastName`

Answer 2

SELECT
    *
FROM
    People
ORDER BY
    CASE WHEN LastName IS NULL THEN 1 ELSE 0 END,
    LastName

You could also simply use

SELECT
    *
FROM
    People
ORDER BY
    COALESCE(LastName, 'ZZZZZ')

Technically, the second version would fail if a person actually had a LastName in your DB of "ZZZZZZ".

NOTE: I'm sure it's just because you're giving an example, but I hope you're not using SELECT * in actual production code... :)

Answer 3

  SELECT *, LastName IS NULL AS nullity 
    FROM `People` 
ORDER BY nullity ASC, `LastName` ASC

Answer 4

this should do it for you

select *, if(isnull(name), 1, 0) as is_name_null 
from names 
order by is_name_null asc, name asc