Have Geek Brain

Getting list of cities and countries from SQL table

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

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 create a list that is ordered by country and city, and each city appears only once in the result.

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

+1


a source to share


3 answers 


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


+9


a source 


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


+1


a source


I figured out how to do this with Linq. It seems to be working fine. Not sure about 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;
            }
        }
+1


a source






More articles:


All Articles