Ordering within groups using LINQ

Question

Given the following data:

List<Country> countries = new List<Country>();

countries.Add(new Country{ Name = "USA",     population=500000,  Year=2012 });
countries.Add(new Country{ Name = "USA",     population=300000,  Year=2002 });
countries.Add(new Country{ Name = "USA",     population=250000,  Year=1992 });
countries.Add(new Country{ Name = "USA",     population=20000,   Year=1982 });

countries.Add(new Country{ Name = "India",   population=1500000, Year=2012 });
countries.Add(new Country{ Name = "India",   population=1000000, Year=2002 });
countries.Add(new Country{ Name = "India",   population=50000,   Year=1982 });
countries.Add(new Country{ Name = "India",   population=80000,   Year=1992 });

countries.Add(new Country{ Name = "Germany", population=100000,  Year=2012 });
countries.Add(new Country{ Name = "Germany", population=400000,  Year=2002 });
countries.Add(new Country{ Name = "Germany", population=60000,   Year=1992 });
countries.Add(new Country{ Name = "Germany", population=4000,    Year=1982 });

countries.Add(new Country{ Name = "UK",      population=450000,  Year=2002 });
countries.Add(new Country{ Name = "UK",      population=50000,   Year=1992 });
countries.Add(new Country{ Name = "UK",      population=3000,    Year=1982 });

I want to order the countries by the largest population for a given year, but then display all the years for that country before moving on to the next country.

E.g.

2012 - the population order would be India, USA, UK, Germany. So I would like the data to be ordered by all India data, all USA data, all UK data and then Germany.
2002 - the population order would be India, USA, Germany and then UK. UK is last because it has no 2002 data.

I want to achieve this using LINQ, although I've used LINQ in the past I'm struggling to get my head around this. Any help would be much appreciated.

You shoule add the result for detail what you expected – cuongle Oct 03 '12 at 16:07 — cuongle, Oct 03 '12 at 16:07

saj · Accepted Answer · 2012-10-03T16:07:51.563

You need to group the data by country and then order by population for a particular year;

var year = 2002;    

var orderedCountries = countries
                        .GroupBy(c => c.Name)
                            .OrderByDescending(c => c.Where(y => y.Year == year).Select(p => p.population).FirstOrDefault())
                                .SelectMany(c=>c)  
                                .ToList();

The above code will work even if there is no data for a given year

That can be a bit much to read so you could separate out the logic in the orderby into a delegate e.g.;

var orderedCountries = countries
                            .GroupBy(c => c.Name)
                                .OrderByDescending(c => GetPop(c, year))
                                    .SelectMany(c=>c)  
                                        .ToList();

Func<IGrouping<string, Country>, int, long> GetPop = 
                                    ((cntry, yr) => (from c in cntry
                                                     where c.Year == yr
                                                     select c.population).First());

This is good but I'd add `.SelectMany(i=>i)` before the `.ToList()` to make it `List` rather than `List>` — Chris, Oct 03 '12 at 16:02
And out of interest why `First` rather than `FirstOrDefault` in the second bit of code? — Chris, Oct 03 '12 at 16:09

score 0 · Answer 2 · answered Oct 03 '12 at 20:36

This is quite equivalent to saj's answer, but using query syntax:

var y = 2002;

var ordered = 
    from c in countries
    group c by c.Name into g
    select new
    {
        Name = g.Key,
        Population = (
            from x in g
            where x.Year == y
            select x.population).FirstOrDefault()
    } into s
    orderby s.Population descending
    join f in countries
    on s.Name equals f.Name
    select f;

score -1 · Answer 3 · edited May 23 '17 at 11:43

-1

There is a similar question asked here: Multiple "order by" in LINQ

Something like this would do the trick in your case. I tested it in VS and it works as expected:

var countries order= countries.OrderBy(c => c.Year).ThenBy(n => n.population);

edited May 23 '17 at 11:43

Community

1
1

answered Oct 03 '12 at 16:02

Joseph T

11
1

1

This isn't what is required. It is not multiple ordering by, but grouping, then ordering. – Chris Oct 03 '12 at 16:04

Ordering within groups using LINQ

3 Answers3