Sort Order for NULLS
-
Can some one tell me the syntax to set the sort order for NULLs in a column.
e.g
SELECT Col1 FROM Table1
NULL
NULL
1
2
3
4
5
I want the result set
1
2
3
4
5
NULL
NULL
-
First of all, without specifying an ORDER BY clause in your query you can never be sure of any specific order for the results.
Now, NULLs are treated as the lowest possible values for sorting purposes, so to get the order you want you need to do custom sorting. This should work:
SELECT Col1 FROM Table1
ORDER BY CASE WHEN Col1 IS NULL THEN 2147483647 ELSE Col1 END ASC
-
Sorry my fault actually the query was.
SELECT Col1 FROM Table1
ORDER BY Col1 ASC
and the result is
NULL
NULL
1
2
3
4
5
Now I use your solution (Using 2147483647) But the error occors that the column used in order by clause should be in select list.
SELECT Col1 FROM Table1
ORDER BY CASE WHEN Col1 IS NULL THEN 2147483647 ELSE Col1 END ASC
Actually i want to set the value of following constant, this will solve my problem, but i dnt know how to set the value of this constant.
Constant Description DBPROPVAL_NC_END NULLs are sorted at the end of th
e list, regardless of the sort order.
-
Actually i want to set the value of following constant
I assume you are talking about the dynamic property "NULL Collation Order" on the ADO connection object, and that you want to set it to the DBPROPVAL_NC_END constant. That property is readonly, so that is not possible.
Regarding my solution, you will of course need to substitute Col1 for whatever yur column is really called. If you have a table called Table1 with a column called Col1 my query would work.
-
Ok Fine....Actually your solution works for select statement of one table but if i use a UNION operator then the following error occors
"ORDER BY items must appear in the select list if the statement contains a UNION operator"
Consider the following query.....the above error will occur on this query
SELECT A.Col1
FROM [Table1] A
UNION
SELECT B.Col1
FROM [Table2] B
ORDER BY CASE WHEN A.Col1 IS NULL THEN 2147483647 ELSE A.Col1 END ASC
-
SELECT Col1
FROM (SELECT A.Col1
FROM [Table1] A
UNION
SELECT B.Col1
FROM [Table2] B) foo
ORDER BY CASE WHEN foo.Col1 IS NULL THEN 2147483647 ELSE foo.Col1 END ASC
-
Thanks alot...i think the problem is near to be solved after the resolution of the following error
Actually in my case the data type of Col1 is DATETIME, now when i use your solution, i am getting the following error.
"Arihtmetic Over flow converting expression to Data type DATETIME"
This error is due to the '2147384647' in the order by cluase
-
Yes, all the values from the case statement needs to be of the same type of course. Use the max datetime value:
SELECT Col1
FROM Table1
ORDER BY CASE WHEN Col1 IS NULL THEN '9999-12-31T23:59:59' ELSE Col1 END ASC
-
thanks alot the problem is solved
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply