sql server - SQL Sort Order with Null values last

Question

I have the following test-code:

CREATE TABLE #Foo (Foo int)

INSERT INTO #Foo SELECT 4
INSERT INTO #Foo SELECT NULL
INSERT INTO #Foo SELECT 2
INSERT INTO #Foo SELECT 5
INSERT INTO #Foo SELECT 1

SELECT * FROM #Foo
 ORDER BY
  CASE WHEN Foo IS NULL THEN Foo DESC ELSE Foo END

DROP TABLE #Foo

I'm trying to produce the following output:

1
2
3
4
5
NULL

"If null then put it last"

How is that done using Sql 2005

/M

Answer 1

One way is to sort it like this:

ORDER BY 
(CASE WHEN Foo IS NULL THEN 1 ELSE 0 END), Foo

Or: First sort by null, then sort by the Foo contents.