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

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 2

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