Possible Duplicate:
Case Order by using Null可能重复:使用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.
我正在查找由“ordernum”字段排序的记录列表。ordernum字段是一个int字段。此字段从NULL开始,直到用户设置。我希望NULL条目出现在列表的末尾。
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?
我知道我可以输入最大可能的整数,但我想用一个变量替换[large]。这是可能的吗?
1 个解决方案
#1
25
I found a way to order NULL values on the bottom.
我找到了一种方法,在底部为空值排序。
http://sqlblog.com/blogs/denis_gobo/archive/2007/10/19/3048.aspx
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
#1
25
I found a way to order NULL values on the bottom.
我找到了一种方法,在底部为空值排序。
http://sqlblog.com/blogs/denis_gobo/archive/2007/10/19/3048.aspx
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