Menu
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
976
If you specify the ORDER BY clause, NULL values by default are ordered as less than values that are not NULL. Using the ASC order, a NULL value comes before any non-NULL value; using DESC order, the NULL comes last.
When you order by a field that may contain NULL values, any NULLs are considered to have the lowest value. So ordering in DESC order will see the NULLs appearing last. To force NULLs to be regarded as highest values, one can add another column which has a higher value when the main field is NULL.
SQL treats NULL values to be less than 0 so while sorting in ascending order, NULL values always appear to be at first.
It depends on the user that, whether to order them in ascending or descending order. The default order is ascending. The SQL ORDER BY clause is used with the SQL SELECT statement. Note: SQL ORDER BY clause always come at the end of a SELECT statement.
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.
68
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.
31
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
