sorting with nulls

[WorkSuxs]

My problem is I need to order by a column that has nulls in, present the records with nulls last AND the ones with data needs to be sorted ascending like below:

specialNumber name
1 paul
10 jill
44 mark
null jane
null bob

 

[checkai]

could do ...

select specialnumber,name
from table
order by isnull(specialnumber,9999999999) asc

 

[SQLSister]

select * from yourtable
order by (case when specialNumber is null then 1 else 0 end) asc, specialNumber asc

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[dbomrrsm]

need to shorten the 9999999999 to 999999999 to stop arithmetic overflow if specialnumber is int

[blue]DBomrrsm[/blue]

 

[WorkSuxs]

I tried both and I'm still getting the nulls first and then the numbers is ascending order. Maybe I'm doing something wrong. Here is the sql I'm sending:

select * from tableName where servtype='Bike' and (ReqDate between '2004-01-01' and '2005-01-01') and Division ='West' order by isnull(PromoCode,'99999999') asc

 

[SQLSister]

Did you try my solution?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[WorkSuxs]

Thanks

Yes, I did and the same problem nulls first then numbers in ascending order.

I have to leave for the day. Please post anything else and I'll try it first thing in the morning.

Thanks for the info (both of you)

 

[SQLSister]

Odd I know my code worked on my test table. I tried it again on a different table and it worked.

Maybe some other part of the query is affecting this? Could you post the whole query that you used?

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[WorkSuxs]

OK, I'm at home. I don't have access to my code, but I had posted my entire sql statement above. For your's (SQLSister) I just exchanged everything after "order by".

 

[donutman]

I think we may have been through this one before. Is there a char(0) at the beginning of your PromoCode field? I see that it's really a varchar column, isn't it?
If there is some sort of unprintable character then put the same unprintable character as a prefix in the 2nd parameter of the IsNull() function.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[WorkSuxs]

Very sorry, what is char(0)? the column is a var with a length of 7. I tried changing the "order by isnull(PromoCode,'9999999') asc" to only 7 in the '9999999'.

 

[WorkSuxs]

OK, I just got it...I'm really embarassed to say, but I was too deep into the problem to remember that NULL and blank are two different things. I put in NULL and it worked just fine.

Thanks everyone who responded.