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.
|Navy Blue Linen||NULL|
|Dark Green Linen||NULL|
|White Coated Two Sides||1|
|White Cast Coated One Side||2|
|White SemiGloss Coated One Side||3|
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]
Like this post? Please share it!