Order by Column Names Containing Null Values in SQL

Order by Column Names Containing Null Values in SQL

There are many scenarios where you have to fetch record order by some column names

but what if the order by column contains NULL values

Then it will order the table by placing NULL Values first then order on sequence what you want

I will Explain it through this scenario

create table #tempcustomer

(Custid int,region nvarchar(40))

insert into #tempcustomer values(1,'AK'),(2,'BC'),(3,Null),(4,'CA'),(5,null)

Desired Output:

Custid region

1 AK

2 BC

4 CA

5 NULL

3 NULL

select Custid,region

from #tempcustomer order by region

if you will run the above query you will get output like below

Output:

 Custid region

3 NULL

5 NULL

1 AK

2 BC

4 CA

select Custid,region

from #tempcustomer order by region desc

Now run above query still you are not getting your desired output

Output:

Custid region

4 CA

2 BC

1 AK

3 NULL

5 NULL

But None of our Queries are giving us actual Result in order to get desired result Use Case Statement in Order by Clause

select Custid,region

from #tempcustomer order by case

when region  IS null then 1

else 0

end

Now you will get your desired Output:

Explanation In case statement first it will Check wheather region is null in our table region has null values so it will order by 1 for Null values  and for other non-Null 
values it will order by 0.so 0 order values comes first then 1 order values.

Note:  The default sort behavior of NULLs in T-SQL is to sort first NUll Values  (before non-NULL values).