Sorting with NULL

[Sharif Islam]

I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation. I
have two fields:
chapterauthor
authors

Here's my query:
SELECT ([chapterauthor] + [authors]) AS CT FROM Items ORDER BY CT
Both Authors and ChapterAuthor can be null. I am trying to figure out if
I can ignore the null value when ChapterAuthor is null.

Sample data/result:
Appadurai, Arjun #chapter author is null
Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)

I would like the output to be like this:
Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)
Appadurai, Arjun #chapter author is null

I guess I can write a stored procedure with if/else, but I was wondering
If I can do this with a simple query. Thanks.

[Erland Sommarskog]

Sharif Islam (mi****@spam.uiuc.edu) writes:

I used SET CONCAT_NULL_YIELDS_NULL OFF do ignore NULL in concatation.

That setting is only for legacy software and there is no reason to use
it new code.

Here's my query:
SELECT ([chapterauthor] + [authors]) AS CT FROM Items ORDER BY CT

SELECT coalesce(chapterauthor, '') + coalesce(authors, '') AS CT
FROM Items

coalesce() is a function that accept a list of values as parameters and
returns the first non-NULL value in the list.

Both Authors and ChapterAuthor can be null. I am trying to figure out if
I can ignore the null value when ChapterAuthor is null.

Sample data/result:
Appadurai, Arjun #chapter author is null
Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)

I would like the output to be like this:
Appadurai, ArjunFardon, Richard (ed.) # (chapterauthor+authors)
Appadurai, Arjun #chapter author is null

I guess I can write a stored procedure with if/else, but I was wondering
If I can do this with a simple query. Thanks.

I am not sure that I understand exactly what you are looking for, but
try:

ORDER BY CASE WHEN chapterauthor IS NOT NULL THEN 1 ELSE 0 END, CT
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx