SQL Server 2005: Order with NULL values at the end
11,066
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
Author by
dangowans
Updated on June 15, 2022Comments
-
dangowans almost 2 years
Possible Duplicate:
Case Order by using NullI'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?
-
Lamak almost 12 yearsThis is the right way to do it. But I find it weird that your answer was posted at almost the exact same time that your question
-
dangowans almost 12 yearsI was Googling at the same time. It's really difficult to search for things like "max int" without getting the aggregate function. It's possible to post a question with answer at the same time, which is what I did. :)
-
Lamak almost 12 yearsI know its possible, no problem with that. But this question is actually a duplicate that has been answered many times here on SO, so a quick search here would've returned this same thing
-
daniloquio almost 12 years@Lamak It is true what dangowans says, look at this blog.stackoverflow.com/2011/07/…
-
daniloquio almost 12 years@Lamak but you are right also, I searched in SO with a fragment of the question title and found this in less than a minute stackoverflow.com/questions/2807361/… Answer your own question is intended as a way of adding some missing piece of knowledge to stackoverflow.
-
dangowans almost 12 yearsI apologize for the duplication. I didn't find the duplicates originally because I was looking for a way to select the largestInt, not to order with NULLs at the end. While working through the question, I figured out what I was really looking for. Sorry.