Thursday, March 10, 2011

Logic in sorting (ASC/DESC) null in sorted column should be always bellow in values MS SqlSever


We need the logic in sorting (ASC/DESC) the null  of sorted column should be always bellow in values.
SQuery for MS SQL SERVER


create table #temp ( inde int, inde2 varchar(20))
insert into #temp values (null,'vvv')
insert into #temp values (1,'zzz')
insert into #temp values (2,'bbb')
insert into #temp values (null,'rrr')
insert into #temp values (null,'tttt')
insert into #temp values (1,'aaaa')

For example in #temp table if we sorted desc order than it should return 2,1,1 null,null,null and when we sort it by ASC than it should 1,1,2,null,null,null
We can achive this by following query


select * from #temp order by (case when inde IS null then 0 else 1 end) desc, inde desc

select * from #temp order by (case when inde IS null then 0 else 1 end) desc, inde asc

Thanks
Mahesh K. Sharma





No comments: