Menu
Possible Duplicate:
Case Order by using Null
I'm looking to get a list of records ordered by an "ordernum" field. The ordernum field is an int field. This field starts as NULL until set by a user. I would like the NULL entries to appear at the end of the list.
I am building a query as follows:
select *, case when (ordernum is null) then [largestInt] else ordernum end as newordernum
from tableName
order by newordernum
I know I could enter the value for the largest possible int for [largestInt], but I would like to replace [largestInt] with a variable. Is this possible?
802
If you sort a column with NULL values in ascending order, the NULLs will come first. Alternatively, if you add a DESC keyword to get a descending order, NULLs will appear last.
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.
SQL ORDER BY Clause Handling NULLS This means that if you specify a column in the ORDER BY clause that has null values in ascending order the NULL values will appear first in the result set. Conversely, if you specify descending order, they will appear last in the result set.
The order statement generates a 'virtual field' that contains NULL or 0 based on the case condition. The ORDER BY is applied based on that virtual field. The zero value in the order by precedes the NULL value. That's all, it's a way to set NULLs of the field1 at the end of the order list.
I found a way to order NULL values on the bottom.
http://sqlblog.com/blogs/denis_gobo/archive/2007/10/19/3048.aspx
It meets my needs quite nicely. My query is now:
select *
from tableName
order by case when ordernum is null then 1 else 0 end, ordernum
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