How will you keep a specific country on top in a drop down list?

Question

In a list of countries that need to be displayed in an alphabetical order, you need to place United States at the top of the list. How will do you this?

I answered by saying that I will have a table structure in such a way that the US is at id-0. The rest of the countries are listed in their alphabetical order.

So when I fetch from the table, I will do a "SELECT CountryName from tableOfCountries ORDER by ID"

I am not sure if the interviewer wanted to hear this. So I suggested another option of populating a country array with the US as the first element. Then the rest of the elements will be populated from the resultset of the query.

"SELECT CountryName FROM tableOfCountries WHERE countryName != 'US' ORDER by COUNTRY_NAME".

This will ensure that the US does not get selected twice.

The interviewer wasn't satisfied with this option either. So he asked me if I had any other option in mind. Then I said a text file on the webserver with the list of values.

Do you have any other options that you can think of?

Answer 1

Maybe:

select name from countries
order by case when name = 'US' then 1 else 2 end, name;

Answer 2

Generally something like:

SELECT 
    CountryName 

from tableOfCountries 

ORDER by 
    (case when CountryName = 'US' then 0 
     else 1 end) asc, CountryName asc

Answer 3

Have another int column in the country table called precedence.

Assign United States a precedence greater than 1 and leave all other countries at 0.

Your SQL would then look like:

select Name from countries
order by precedence desc, name asc

This will allow you to scale this later if need be.

Answer 4

I certainly wouldn't abuse ID that way if I could avoid it.

One could give priorities to countries and then:

select isoCode, name from countries order by priority desc, name

Alternatively, why have it at the top? I'd suggest changing the logic so that they remained in alphabetical order, but US was the default selection until changed.

Answer 5

If this is in C#, disregarding how the data comes back from the server, placing them in a specific type and implementing IComparable on the given type. The drop down will then hold them accordingly as you can specify in the CompareTo method that the US type is the "greater" of the other countries.

class Country : IComparable<Country>
{
    int IComparable<Country>.CompareTo(Country other)
    {
        <US logic>
        else
            return String.Compare(this.CountryName, other.CountryName);
    }
}

EDIT: Actually this is in C# based on the OP's tags. Why the focus on the back end for presentation is taking place is beyond me.

Answer 6

Another way to do it would be to get your list of states and then filter them into a dataview, excluding the default items. Then set the list control property AppendDataBoundItems to true. Add your items to the list control and then databind to the dataview.

http://msdn.microsoft.com/en-us/library/system.web.ui.webcontrols.listcontrol.appenddatabounditems.aspx

This will have the effect of not clearing your default values when the databind occurs.