MySQL ORDER BY ASC with NULL values last

[Marius]

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?

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