Using SQL UNION queries to populate the first Item in a list type control
Part two of a two-part series of blogs

Adding a first or default item to a list control which gets its data from a table can be tricky. This blog gives an elegant solution using UNION queries, which works equally well for ASP.NET dropdown controls, Windows Forms dropdowns or Access combo boxes.

  1. Set the first item in a list type control
  2. Creating the Union Query for a List Type Control (this blog)

Posted by David Wakefield on 31 May 2011 | no comments

You need a minimum screen resolution of about 700 pixels width to see our blogs. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.

Creating the Union Query for a List Type Control

We can start with a simple select query:

SELECT PersonId, PersonName

FROM tblSinger

The following query returns the value we want in the top of our list:

SELECT 0 , 'CHOOSE A SINGER'

It does not have a FROM clause so it just returns the hard coded values.

I have chosen 0 as the value for this item, as none of the underlying data has a key field equal to zero. This would allow me to write code which checked the default value had not been selected.

Now we can use a union clause to join the two statements together:

SELECT 0 , 'CHOOSE A SINGER'

union

SELECT PersonId, PersonName

FROM tblSinger

Selected data
 

In a UNION query the column names are taken from the first SELECT statement, so we now add column aliases to the first SELECT:

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName

union

SELECT PersonId, PersonName

FROM tblSinger

Data with column headings

Now we need to sort the names alphabetically. The following does not work as expected:

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName

union

SELECT PersonId, PersonName

FROM tblSinger

ORDER BY PersonName

Data poorly sorted 'CHOOSE A SINGER' is no longer at the top of our list as an

To order items correctly we need to add another column. I have called this MySortOrder, and it does not exist in the underlying table. This will have constant values of either 0 for the first value and 1 for every value from the table.

SELECT 0 AS PersonID , 'CHOOSE A SINGER' AS PersonName, 0 as MySortOrder

union

SELECT PersonId, PersonName, 1 as MySortOrder

FROM tblSinger

ORDER BY MySortOrder, PersonName

The final ORDER BY clause now sorts by MySortOrder which will put the one record with 0 at the top of the result list and all the others below sorted by PersonName.

The final sorted result
 

You can use this SQL either in an SQL View or as the source of your databound control!