Case Order by using Null

Question

Hello

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.

Answer 2

You can also do

SELECT * FROM #Foo ORDER BY COALESCE(Foo, 2147483647)

which will replace NULL with the largest possible int for the purposes of sorting only (so leaving the retured values alone) and so shunt it to the back of any order.