Placement of Null in result set - sort order

Hi All,
This is a really crappy question that I found a responce to ages ago, but cannot find on the forum just now.

Whe I sort by a column that has null, then the NULL values are either in the top or bottom of the result set depending on whether the order by is asc or desc.

If I have a table
t with a column c which has 5 rows with values 1,2,3,4,NULL
************************************

create table #t(c int)

insert into #t values(1)
insert into #t values(2)
insert into #t values(3)
insert into #t values(4)
insert into #t values(null)

*************************
Then.....
select *
from #t order by 1 asc
results in ....
NULL
1
2
3
4

*******************
and...
select *
from #t order by 1 desc
results in.,...
4
3
2
1
NULL

I need null always at the end. so that I either get
1
2
3
4
NULL

or

4
3
2
1
NULL

Any ideas?

PeterYou have this problem only with ASC. In this case, order like this:

order by isnull(c, 2^31 - 1 ) ASC

instead of

order by 1 ASC|||Yo,
why are you using a bit operator.
can't I just use...
select *
from #t order by isnull(c, 4000 )|||Actually, it isn't a bit operator, but the Power operator. Anything is fine which is larger than your largest number to be expected of c.