SQL Server NULL values and "Order By" order

19 May, 2008 sql

I have a few tables that contain a column called "Order", which is used to sort by when retrieving the data. The purpose is to keep the data in a certain order when displayed to the end user.

Black Linen	NULL
Navy Blue Linen	NULL
Dark Green Linen	NULL
Burgundy Linen	NULL
Ivory Vellum	NULL
Grey Felt	NULL
Natural Linen	NULL
White Coated Two Sides	1
White Cast Coated One Side	2
White SemiGloss Coated One Side	3
White Smooth	4
White Linen	5

The problem is that SQL Server puts null values above non-null values when doing an "order by". To reverse this behavior, this was the most elegant and efficient solution that I found:

Select FooValue
From foos
Order by (Case When [Order] Is Null Then 1 Else 0 End), [Order]

I found information about the original problem here, and the solution was from Tim in the comments. Thanks!

9 Comments so far »

Ska said,

Wrote on August 8, 2008 @ 10:43 am

Thanks a lot for this Jason.
Andra P žurnāls - web izstrāde, pārlūku jaunumi, pamācības, programmas » SQL kārtošana un NULL vērtības said,

Wrote on October 14, 2008 @ 11:43 am

[...] Risinājuma oriģināls. [...]
Gints said,

Wrote on October 15, 2008 @ 8:25 am

SQL standard has NULLS FIRST / NULLS LAST syntax for that. Hopefully next version of SQL Server will implement that
V_OMAR said,

Wrote on September 21, 2010 @ 11:55 am

Gracias por tu aporte me ayudo mucho
Muhammad Atif Riaz said,

Wrote on December 8, 2010 @ 6:41 am

SELECT rank
FROM blah
ORDER by rank

will give you following results

Rank
—-
null
null
null
1
2
3

and

SELECT rank
FROM blah
ORDER by rank desc

will give you following results

Rank
—-
1
2
3
null
null
null

why bother such complex solution
Cory said,

Wrote on December 10, 2010 @ 1:39 pm

@Muhammad Atif Riaz:

Correction: SELECT rank
FROM blah
ORDER by rank desc

will give you following results

Rank
—-
3
2
1
null
null
null

Clearly there’s a need for this “complex solution” and it works as intended.

Thanks Jason (and Tim)!
bp said,

Wrote on January 16, 2011 @ 5:06 pm

How about..

SELECT rank
FROM blah
ORDER by rank * -1
Anthony Mills said,

Wrote on September 22, 2011 @ 2:40 pm

Excellent idea bp!

SELECT rank
FROM table
ORDER BY -rank DESC

Yeah, if you order by negative rank descending, you get the normal sort but with nulls at the end.
Reydmon said,

Wrote on September 28, 2011 @ 7:32 pm

@bp said

And.. what happens when the column is not numeric??
or when is a result of a subquery

the Jason’s solution works!!! in any case