Linq: how to select top X record with multi groupby

Question

I have a List

List[0]: .colA:"Red". colB:"Big".colC:"2020".colD:"info0a".colE:"info02".

List[1]: .colA:"Red". colB:"Big".colC:"2021".colD:"info1a".colE:"info12".

List[2]: .colA:"Red". colB:"Big".colC:"2022".colD:"info2a".colE:"info22".

List[3]: .colA:"Red". colB:"Small".colC:"2021".colD:"info3".colE:"info32".

List[4]: .colA:"Red". colB:"Small".colC:"1999".colD:"info4a".colE:"info42".

List[5]: .colA:"Blue". colB:"Small".colC:"2023".colD:"info5a".colE:"info52".

List[6]: .colA:"Blue". colB:"Small".colC:"2025".colD:"info5a".colE:"info52".

I want ,first groupby for colA and colB,then select the oldest record according to colC, so the result should be

colA:"Red".colB:"Big".colC:"2020".colD:"info0a".colE:"info02".(for Red,Big,oldest is 2020)

colA:"Red".colB:"Small".colC:"1999".colD:"info4a".colE:"info42".(Red,Small,oldest 1999)

colA:"Blue".colB:"Small".colC:"2020".colD:"info5a".colE:"info52".(Blue,Small, oldest 2023)

May I know how to do it? Thank you very much

Answer 1

To achieve the desired result, you'll need to perform a group-by operation on colA and colB, then select the oldest record based on colC.

Here is the c# sample code.

using System;
using System.Collections.Generic;
using System.Linq;

public class Record
{
    public string colA { get; set; }
    public string colB { get; set; }
    public string colC { get; set; }
    public string colD { get; set; }
    public string colE { get; set; }
}

class Program
{
    static void Main()
    {
        var records = new List<Record>
        {
            new Record { colA = "Red", colB = "Big", colC = "2020", colD = "info0a", colE = "info02" },
            new Record { colA = "Red", colB = "Big", colC = "2021", colD = "info1a", colE = "info12" },
            new Record { colA = "Red", colB = "Big", colC = "2022", colD = "info2a", colE = "info22" },
            new Record { colA = "Red", colB = "Small", colC = "2021", colD = "info3", colE = "info32" },
            new Record { colA = "Red", colB = "Small", colC = "1999", colD = "info4a", colE = "info42" },
            new Record { colA = "Blue", colB = "Small", colC = "2023", colD = "info5a", colE = "info52" },
            new Record { colA = "Blue", colB = "Small", colC = "2025", colD = "info5a", colE = "info52" }
        };

        var oldestRecords = records
            .GroupBy(r => new { r.colA, r.colB })
            .Select(g => g.OrderBy(r => r.colC).First())
            .ToList();

        foreach (var record in oldestRecords)
        {
            Console.WriteLine($"colA: {record.colA}, colB: {record.colB}, colC: {record.colC}, colD: {record.colD}, colE: {record.colE}");
        }
    }
}