Using order by with nulls in T-SQL

Question:
You have a table with EmpName and DateOfLeaving columns.
The EmpName column is mandatory, but DateOfleaving will have null value for those employees who have not left the company.
The question is to have list off all employees with the employees with DateOfLeaving coming last and all others sorted ascending order of date of leaving, Name sorted in ascending order for each group.
Consider the Example:

Table: Employees

EmpName DateOfLeaving

Abc 10 Oct 1999

Bcd 11 Nov 1998

Ccd null

Dcd 10 Aug 2000

Eed null

The solution should be:

EmpName DateOfLeaving

Bcd 11 Nov 1998

Abc 10 Oct 1999

Dcd 10 Aug 2000

Ccd null

Eed null

Which query will produce this output?

Answer:

• Select EmpName, DateOfLeaving from Employees order by isnull(DateOfLeaving,’10/10/9999’),EmpName asc
• Select EmpName, DateOfLeaving from Employees order by DateOfLeaving desc, EmpName asc
  

Explanation:
In SQL Server T-SQL, by default, a query takes null as first when the order is ascending (the default). So we just give some value of greater date in order by so that it will come at last.

Note: The question of day is taken from SqlServerCentral.com. I want to compile selected QOD to share.