SQL Server – Selecting Top 1 in a Left Join

This scenario doesn’t come up too often, but the other day I had a requirement to do a left join through a one to many relationship but only get 1 result from the right side of the join. This kind of scenario can often be accomplished using sub selects, but in this case I needed access to multiple columns so a sub select would be out of the question.

The solution was to do an OUTER APPLY instead of a LEFT JOIN. An outer apply allows you to write a sub query referencing the ID from the original table just like a sub select. However as this is effectively a kind of join you have access to multiple columns.

Example Scenario

Imagine you have a table of customers and a table of addresses and each customer can have multiple addresses.

Our tables will look like this:

Customer (CustomerID, FirstName, LastName)

Address (AddressID, CustomerID, Line1, Line2, Town, County, Country, DateAdded)

The task is to get a list of customers and their most recent address.

Solution

By using an OUTER APPLY we can join the 2 tables and get the most recent address like this:

SELECT c.*, la.*
FROM Customer c
OUTER APPLY 
    (SELECT TOP 1 *
    FROM Address a
    WHERE a.CustomerID = c.CustomerID
    ORDER BY a.DateAdded DESC
    ) AS la

Advertisements

Theme Manager for Windows Phone

WP Themes

One of Windows Phones features is the ability for a user to switch between light and dark themes. However as a developer creating a good experience in your apps that looks good in both themes can be a challenge. What’s worse is it sometimes leads to apps that look great in one theme but not the other.

Unfortunately there is also no official easy way to tell the phone you want your app to run with a particular theme. Instead it’s a manual process of either setting the colour of every object, or manually loading a theme in the apps start-up.

Thankfully there is a better way and it comes in the form of a NuGet package. By simply installing the wp-thememanager package you can call ThemeManager.ToLightTheme() in your apps contructor to make the theme light, or ThemeManager.ToDarkTheme() to make it dark.

/// <summary>
/// Constructor for the Application object.
/// </summary>
public App()
{
    // Global handler for uncaught exceptions.
    UnhandledException += Application_UnhandledException;
 
    // Standard Silverlight initialization
    InitializeComponent();
 
    // Phone-specific initialization
    InitializePhoneApplication();
 
    ThemeManager.ToLightTheme();
 
    // Other code that might be here already...
}

 

Links to resources

WP Theme Manager on GitHub
Jeff Wilcox blog post on the project
NuGet Package for WP Theme Manager