Home » Databases » FW: Microsoft SQL Server – Sort Values Ascending But NULLS Last

FW: Microsoft SQL Server – Sort Values Ascending But NULLS Last

This is a frequent request in newsgroups and fora. People want to sort the column in ascending order but don’t want the NULLS at the beginning.Oracle has this syntax:

ORDER BY ColumnName NULLS LAST;

SQL Server does not have this. But there are 2 ways to do this. The first one is by using case and the second one by using COALESCE and the maximum value for the data type in the order by clause.

Read more at SQL Server Code,Tips and Tricks, Performance Tuning. These two methods will also work on Sybase ASE if you use a derived table or temp table instead of the memory table. 🙂

Share Button

Comments

  1. BryanTheCrow over on Digg.com suggested:
    Easier way:

    ORDER BY IsNull(ColumnName, @ValueThatsBiggerThanEverythingElse)

    Just replace the @ValueThatsBiggerThanEverythingElse variable with what it says (I didn’t fill it in because it’d be different depending on the data type of your column). For DESC sorting, just make the value smaller than everything else.

  2. Jim says:

    Oh cool, thanks for the info.

Leave a Reply

Your email address will not be published. Required fields are marked *

*
*

Facebook login by WP-FB-AutoConnect