Brettb.Com
  HOME | ABOUT ME | BIOTECHNOLOGY | ARTICLES | GALLERY | CONTACT
Search: Go
TECHNICAL ARTICLES
 ASP
 ASP.NET
 JavaScript
 Transact SQL
 Other Articles
 Software Reviews

PHOTO GALLERIES
 Canon EOS 300D Samples
 Akihabara Maids!
 More Galleries...

TRAVEL LOG
 2009: China
 2008: Tokyo
 2007: Tokyo
 2006: Hong Kong
 2005: New York City

MORE STUFF
 Search Engine Optimisation
 Build an ASP Search Engine
 My Tropical Fishtank
 Autoglass
 SQL Month Name
 SQL Get Date Today
 SQL Year Month
 Other New Stuff...

POPULAR STUFF
 Regular Expressions
 Index Server & ASP
 JavaScript Ad Rotator

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:

t_NewsArticles:
NewsArticleID int
NewsArticleTitle varchar(255)
NewsArticleText text
fk_CategoryID
(foreign key to t_Categories.CategoryID, can be null)

t_Categories:
CategoryID int
CategoryName varchar(50)


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

AS

select
    NewsArticleID,
    NewsArticleTitle,
    NewsArticleText,
    t_NewsArticleCategories.NewsArticleCategory,
    NullOrderingReversal = case fk_CategoryID when null then 'Z' else 'A' end
   

from
    t_NewsArticles

left outer join t_Categories on t_NewsArticles.fk_CategoryID = t_Categories.CategoryID

order by
    NullOrderingReversal, t_Categories.CategoryName desc

Useful Links

  • Reverse the order of results with the SQL Order Desc Ordering Statement.

  Site Map | Privacy Policy

All content is 1995 - 2012