SQL: Order By with NULL Values Last

I ran across a situation today where I needed to sort a result set on a column that could contain NULL values. By default, all rows with a NULL value in the sorted column are returned first but in this case, I wanted to have all the NULL values show up last in the result set.

Click here to jump to the correct solution or keep reading for a detail explanation of failed attempts at solving this problem.

For example, imagine you have an Employees table as defined below and with the indicated data.

Running a simple query ordering by the nullable DepartmentId column produces the following result (NULL values at the top).

SELECT * 
FROM Employees
ORDER BY DepartmentId

Ordering the result set by DepartmentId descending obviously won’t work as even though the NULL values will appear at the bottom all the other rows will be in descending order as well.

SELECT * 
FROM Employees
ORDER BY DepartmentId DESC

I have seen some people attempt to solve this problem using the following query.

SELECT Id, FirstName, LastName,
	   (CASE 
			WHEN DepartmentId IS NULL THEN 99999
			ELSE DepartmentId
		END) AS DepartmentId	   
FROM Employees 
ORDER BY DepartmentId

While this will most likely sort the rows as desired, this presents multiple problems. First, you are specifying a DepartmentId of 99999 for rows that really don’t have a DepartmentId and second, you have to ensure that you never have a DepartmentId over 99999 in your Employee records.

You can eliminate the first problem of the query above by doing the following:

SELECT *	 	   
FROM Employees 
ORDER BY ISNULL(DepartmentId, 99999)

The ISNULL function will replace a NULL DepartmentId with the value 99999, thus giving the desired ordering while still preserving the NULL value in the result set. But, this still leaves us with the second issue mentioned above.

The Solution

Both issues can be resolved by a simple case statement in the ORDER BY clause as shown below.

SELECT *	   
FROM Employees 
ORDER BY (CASE 
			WHEN DepartmentId IS NULL THEN 1 
			ELSE 0 
	      END), 
	     DepartmentId

Here, the result set is first sorted by a temporary column with the value of 1 if the associated row’s DepartmentId value is NULL and 0 if it is not. Doing such will ensure that all records with a NULL DepartmentId appear after records with a non-NULL DepartmentId. The two groups of records (ones with a NULL DepartmentId and ones with a non-NULL DepartmentId) are then sorted by the DepartmentId which ensures that all records with a DepartmentId are sorted correctly.

A simple solution to a random problem.

C#: String Concatenation using StringBuilder and LINQ Aggregate Function

I always forget how to do this so I thought I would post it mainly for my reference.

List<string> nameList = new List<string>() { "Roger", "Clark", "Wonda", "Anita" };

// A useless Select call in this instance just to remind you
// that you coulde use LINQ to select a set of strings
// from a list of objects
StringBuilder names = nameList.Select(n => n)
                              .Aggregate(new StringBuilder(), (current, next) => current.Append(next).Append(", "));

// Remove the trailing comma and space
if (names.Length > 1)
    names.Remove(names.Length - 2, 2);

Console.WriteLine(names.ToString());

// Output
// ------
// Roger, Clark, Wonda, Anita

C#: Yield Statement Used to Reduce Accessor Code

Often in Windows Forms development I run into the situation where I need to show a list of items to a user from which they can select multiple items. Usually I will accomplish this using a dialog form with a CheckedListBox. The user simply checks the box next to each item they want to select and then clicks OK. For example, here is one that allows a user to override various form letter merge fields.

Before today, I would include an accessor method in the form class similar to the following:

public List<string> SelectedFields
{
    get
    {
        List<string> selectedFields = new List<string>();

        foreach (var item in this.listBxMergeFields.CheckedItems)
            selectedFields.Add(item.ToString());

        return selectedFields;
    }
}

I always thought it was a pain to have to instantiate a new List object and populate it when I wasn’t performing any operations on the List other than adding items. Enter the yield statement. It turns out that the following code can be used to solve my little annoyance.

public IEnumerable<string> SelectedFields
{
    get
    {
        foreach (var item in this.listBxMergeFields.CheckedItems)
            yield return item.ToString();
    }
}

When used in this fasion, the yield statement will return the accumulated values that result from the expression to its right. Pretty handy way to tidy up the code if you ask me! For more information and uses, check the MSDN documentation here.

(I admit this is a relatively trivial way of using this as we are just enumerating over one collection to create another, but the concept can be applied to other situations.)

Windows Gadget: WordPress Statistics

I just wanted to mention that I released an updated version of my WordPress Stats Windows gadget. This release fixes a small inconvenience that occurs when waking the computer up from sleep or hibernation. On such an event a message box appears indicating that there is no internet connection. The problem occurs because a request is sent directly to WordPress.com when the computer wakes up before an internet connection can be established. The gadget will now not show an error message until two consecutive request fail due to a lack of internet connection.

The gadget can be downloaded here.

C#: Method with Arbitrary or Variable Number of Parameters

Normally when I create a method that needs to accept an arbitrary or variable number of parameters or arguments I just accept an array of values. This works great but it does require you to instantiate a new array and populate it every time you want to call the method. Not a big deal but as you can see below it makes the code a bit messy.

static void Main(string[] args)
{
    CountParameters(new int[] { });
    CountParameters(new int[] { 5, 3, 6 });
    CountParameters(new int[] { 1, 2, 3, 4 });
    CountParameters(new int[] { 8, 3, 78, 234, 2, 56, 87 });
}

public static void CountParameters(int[] numbers)
{
    Console.WriteLine(numbers.Length + " parameters passed in");
}

// Output
// ------
// 0 parameters passed in
// 3 parameters passed in
// 4 parameters passed in
// 7 parameters passed in

But, if you simply add the ‘params’ keyword before the parameter definition, this allows you to call the method by just specifying a comma delimited list of arguments.

static void Main(string[] args)
{
    CountParameters();
    CountParameters(5, 3, 6);
    CountParameters(1, 2, 3, 4);
    CountParameters(8, 3, 78, 234, 2, 56, 87);
}

public static void CountParameters(params int[] numbers)
{
    Console.WriteLine(numbers.Length + " parameters passed in");
}

// Output
// ------
// 0 parameters passed in
// 3 parameters passed in
// 4 parameters passed in
// 7 parameters passed in

You can also specify other parameters for the method but the parameter with the ‘params’ keyword must be the last parameter definition. Further you can only add the ‘params’ keyword to one parameter in the list of parameters. More information about the ‘params’ keyword can be found here.

Interestingly, if you look at the disassembly of the second code segment above, you will see the following:

private static void Main(string[] args)
{
    CountParameters(new int[0]);
    CountParameters(new int[] { 5, 3, 6 });
    CountParameters(new int[] { 1, 2, 3, 4 });
    CountParameters(new int[] { 8, 3, 0x4e, 0xea, 2, 0x38, 0x57 });
    Console.ReadLine();
}

public static void CountParameters(params int[] numbers)
{
    Console.WriteLine(numbers.Length + " parameters passed in");
}

It simply instantiates a new array of integers and passes it to the CountParameters method as we did in the first approach. So this is obviously just a way to make your code a bit more readable.