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

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 2

Generally something like:

SELECT 
    CountryName 

from tableOfCountries 

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