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.

Posted in Sorting, SQL. Tags: , . 4 Comments »

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.

LINQ: Selecting Multiple Properties from a List of Objects


In a previous post I discussed how to use LINQ to flatten a list of lists. Yesterday and ran across a slightly different situation but that used the same solution.

I had a list of objects that each contained three separate integer values and I wanted to extract the three values from each object and put them all in a single list. So, all you need to do is create a new List that has each of the three values in each object as elements of the list and then use the SelectMany method to flatten the list of lists we just created.

private class Foo
{
    public int Item1;
    public int Item2;
    public int Item3;
}

static void Main(string[] args)
{
    List<Foo> foos = new List<Foo> 
                           { 
                               new Foo() { Item1 = 1, Item2 = 2, Item3 = 3 },
                               new Foo() { Item1 = 4, Item2 = 5, Item3 = 6 },
                               new Foo() { Item1 = 7, Item2 = 8, Item3 = 9 }
                           };

    // Create a list of lists where each list has three elements corresponding to 
    // the values stored in Item1, Item2, and Item3.  Then use SelectMany
    // to flatten the list of lists.
    var items = foos.Select(f => new List<int>() { f.Item1, f.Item2, f.Item3 }).SelectMany(item => item).Distinct();

    foreach (int item in items)
        Console.WriteLine(item.ToString());
    
    Console.ReadLine();
}

// Output
// ------
// 1
// 2
// 3
// 4
// 5
// 6
// 7
// 8
// 9
Posted in LINQ. Tags: , . 2 Comments »

Windows Gadget: WordPress Statistics


For those of you who don’t like to read, this Windows Gadget can be downloaded from CodePlex here.

I have never actually used a Windows Gadget as I’m kind of a neat freak when it comes to my desktop. But, a couple of weeks ago I actually thought of a gadget that I would actually use: a WordPress gadget that allowed me to see view statistics of my blog as well as provide a few shortcuts to help administer my blog. I searched the internet and found one that has been developed that provides links to various actions on your blog here, but it didn’t provide any statistical information. So, I decided to create my own gadget.

For those who aren’t familiar with the development of a Windows Gadget, it is simply a set of web pages. When I discovered this, my first question was whether or not I could use Silverlight to develop my gadget and it turns out you can! I found a Visual Studio Project template created by Ioan Lazarciuc here that does all the setup and configuration for you. It is a great starting point and allows you to get right to the development instead of having to waste time figuring out which javascript methods have to called when, what the required markup is for the html pages, etc.

Here is a run through of the gadget:

Out of the Box – When you startup the gadget for the first time you will notice the “Configuration required” text at the bottom. Before you can view your stats and manage you blog you need to enter your blog information. Click on the wrench to open the gadget settings.

Settings – To use this gadget it must know two things: your blog uri, and your blog API key. The API key is like a password provided to you by WordPress and allows programs like this Windows Gadget to access statistical information. You can find it by logging into your WordPress account and clicking Users -> Personal Settings on the left hand navigation bar. At the top of the page you will see your API key. Copy and paste that into the Blog API Key text box.

Alternatively, you can enter your blog uri and click the Show Me link and a web browser will be opened directly to the page containing your API key.

Statistics – After you define the gadget settings and you click OK, the statistics for you blog will be loaded and you should see a line graph like the one pictured above.

Menu Options – If you click the arrow on the bottom right corner of the gadget you will see various menu items that will allow you to administer your blog. If you double click on any of them, you will be taken directly to the corresponding page.

Since this gadget was written with the Silverlight 4 Framework, you must have Silverlight installed on your machine for it to run. If you don’t have it installed, you will see the following image appear when you place the gadget on your desktop. If you click on the image, Silverlight will be downloaded and installed.

64-bit Users: If you have a 64-bit system, this image will appear even if you have Silverlight installed. The problem is that there is no 64-bit Silverlight Framework (just as there is no 64-bit version of Flash) but there is a 64-bit Windows Sidebar application which is started by default if you have a 64-bit system. Thus, if you want to run this gadget, you will need to have the 32-bit version of the Windows Sidebar application startup when Windows starts. John McClelland has posted instructions here on how to solve this problem (it says it is for Windows Vista but it works on Windows 7 as well).

This Windows Gadget can be downloaded here and the source code is available here. I consider this project still in beta but I currently use it every day and it appears stable enough for release. Let me know if you run into any issues by posting to the Issue Tracker on CodePlex.

Resources – I built this Silverlight based Windows Gadget using the following tools

Follow

Get every new post delivered to your Inbox.

Join 69 other followers