MySQL ORDER BY ASC with NULL values last

Discuss coding issues, and scripts related to PHP and MySQL.
Marius
Posts: 107

MySQL ORDER BY ASC with NULL values last

Hello
I want to make a Select SQL query that sorts ascending by a column, but the rows with NULL values to come last.
This is the query I use (but the NULL values not come last)

Code: Select all

$sql = "SELECT * FROM mp_tab WHERE col < 50 ORDER BY col ASC";
Is it possible to order by ascending with nulls at the end of the output?

Admin Posts: 805
Hello
You could use the ISNULL() function:

Code: Select all

$sql = "SELECT * FROM mp_tab WHERE col < 50 ORDER BY (ISNULL(col) OR col =''), col ASC"; 
Or, in the ORDER BY clause use CASE to make NULLS have a higher value than anything else:

Code: Select all

$sql = "SELECT * FROM mp_tab WHERE col < 50 ORDER BY (CASE WHEN col IS NULL then 1 ELSE 0 END),col ASC";