sql: order by ascending order with null at the end

by prettyscripts on 2011-07-25 11:38

sql

when using 'order by' clause to sort columns in ascending order, rows with null value comes first.

what if the requirement is to sort in ascending order but rows with null values comes last?

use case in order by statement.

Code:

select * from the_table
order by case when the_column is null then 1 else 0,
    the_column

this will put all rows with null values at the end first, then sort by the values in the column.

read this for further explanation, which is used as reference to for post.