SQL Server NULL values and "Order By" order
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!

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