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

• The SQL Documentation Tool builds technical documentation for Microsoft SQL Server databases. A free trial version is available for download.

• The ASP Documentation Tool can automatically document your ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An ASP.NET Documentation Tool is also available!

Useful Development Tools

SQL Documentation Tool

The SQL Documentation Tool creates technical documentation for Microsoft SQL Server 7.0 and 2000 databases. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced. The SQL Documentation Tool documents SQL Server Tables, Views, Stored Procedures, Triggers, Table Relationships and Dependencies.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (10.3Mb ZIP file).

ASP Documentation Tool™

Automatically creates developer documentation for ASP 2.0 and 3.0 web applications written in VBScript and JScript. Documentation for Microsoft Access, SQL Server 7/2000 databases and Visual Basic 6.0 components associated with the web application can also be incorporated into the reports. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5.2Mb ZIP file).

.NET Documentation Tool

Automatically creates technical documentation for .NET Framework Windows and ASP.NET applications written in C# or VB.NET and SQL Server 7/2000/2005 or Microsoft Access databases associated with the application. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (5Mb ZIP file).

VB Documentation Tool

The VB Documentation Tool creates technical documentation for Microsoft Visual Basic 6.0 projects. Technical documentation is created in HTML and HTML Help formats. The HTML Help format documentation is fully searchable and cross referenced.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1Mb ZIP file).

Indexing Service Companion

The Indexing Service Companion is a Windows application that extends the functionality of the Microsoft Windows Indexing Service so that it is able to index content from remote websites and also from ODBC databases. As such it can be used as a low cost alternative to Sharepoint Portal Search Services.

Try Sample Search Facility.     Download Trial Version (1.7Mb ZIP file).

The Website Utility

The Website Utility examines websites for errors and areas that need to be optimised for search engines by using a built in web crawling engine. Errors checked for include broken or moved hyperlinks, missing page titles and missing meta tags. It also generates HTML for use in creating website site maps (table of contents pages - like this one), and is able to create both client-side JavaScript Search Engines and server-side ASP Search Engines for a website.

View Sample Output (HTML Format).     Download Trial Version (3Mb ZIP file).

PHP Documentation Tool™

Automatically creates developer documentation for PHP web applications. Documentation is created in HTML, HTML Help and plain text formats.

View Sample Output (HTML Help format).     View Sample Output (HTML Format).     Download Trial Version (1.0Mb ZIP file).