Canon EOS 300D
2006: Hong Kong
2005: New York City
Search Engine Optimisation
Build an ASP Search Engine
My Tropical Fishtank
SQL Month Name
SQL Get Date Today
SQL Year Month
Other New Stuff...
Server & ASP
Home > Articles > Transact SQL Programming Articles
Making Null Values Appear Last in SQL Server Result Sets
If you want to specify in which order SQL Server returns records, then you can
obviously use the ORDER BY clause. However, by default, SQL Server's
ORDER BY clause returns records with null values for the column being sorted at the top.
This may not be ideal for a scenario such as a list of news articles sorted that may
optionally be assigned to a category.
In this example, news articles are in the t_NewsArticles table and they are optionally
assigned a category from the t_Categories table:
fk_CategoryID (foreign key to t_Categories.CategoryID, can be null)
An ASP.NET web page displaying the news articles needs to display them sorted by category,
with categories in alphabetical order. News articles with no category will be displayed at
the bottom of the page.
Obviously using ORDER BY CategoryName DESC would put nulls at the bottom of the records,
but this would cause the news article categories to also show in reverse alphabetical
order (i.e. Z to A).
However, by using a CASE...WHEN statement in the SQL, it is possible to
add an additional NullOrderingReversal column to the records that will force the
news articles without a category assigned to them to be listed last:
CREATE PROCEDURE sp_GetNewsArticles
NullOrderingReversal = case fk_CategoryID when null
then 'Z' else 'A' end
left outer join t_Categories on t_NewsArticles.fk_CategoryID = t_Categories.CategoryID
NullOrderingReversal, t_Categories.CategoryName desc
- Reverse the order of results with the SQL
Order Desc Ordering Statement.