Getting list of cities and countries from a SQL Table

Question

I have a SQL table where in each row I store the country and the city of a things location. For example:

record1, New York, USA
record2, Rome, Italy
record3, Milano, Italy
record3, Birghiman, UK 
record4, London, UK
record5, London, UK
record6, London, UK
record7, Birmingham, UK

I would like to generate a list that is ordered by country and city, and each city show up only once in the result.

I would like to know how to solve this in SQL and Linq To SQL in an elegant way.

Answer 1

SELECT MIN(record) AS record, City, Country
FROM [MyTable]
GROUP BY City, Country
ORDER BY Country, City

Answer 2

select distinct country, city
from <Table>
order by country, city;

Answer 3

I figured out how to do this with Linq as well. It seems to be working ok. Not sure about the performance though

        var result = from p in table
                     group p by p.country into country_group
                     select new
                     {
                         country = country_group.Key,
                         cities = from ci in country_group
                                  group ci by ci.city into city_group
                                  select new { city = city_group.Key, cig = city_group }
                     };                          


        foreach(var co in result)
        {
            string country = co.country; 

            foreach(var ci in co.cities)
            {
                string city = ci.city;
            }
        }