Home > database >  How to do GroupBy using Linq with min?
How to do GroupBy using Linq with min?

Time:10-17

I have to display movie which are group by GoodsCode from the list and then get Max Value of ChangeDate on each Goods.

TABLE - GOODS_TABLE
GOODS_CODE GOODS_PRICE CHANGE_DATE GOODS_PART
8800       1000        20220911    1
8800       1200        20220914    1
8801       1300        20220928    2
8801       1500        20220929    2
8802       1300        20220916    3
8802       1500        20221001    3
8802       2400        20221004    3

Example, in that case I want to get it from GOODS_TABLE, like

GOODS_CODE GOODS_PRICE CHANGE_DATE GOODS_PART
8800       1200        20220914    1
8801       1500        20220929    2
8802       2400        20221004    3

in that case,

from p in goods
group p by { p.goods_code, p.goods_price, p.change_date, p.goods_part } into g
select (goods_code: g.Key.goods_code, goods_price: g.Key.goods_price, goods_part : g.Key.goods_part, change_date: g.Max(p => p.change_date));

I wonder if I should bring all the columns that I want to bring with me as above.

Is there any other way in Linq?

CodePudding user response:

try this

var result = goods.GroupBy(g => g.goods_code)
                  .SelectMany(s => s.OrderByDescending(d => d.change_date)
                                    .Take(1));

CodePudding user response:

try this

var result = goods.GroupBy(x => x.goods_code).Select(x => x.MaxBy(y => y.change_date));

example:


class Goods
{
    public int GoodsCode { get; set; }

    public int GoodsPrice { get; set; }

    public int ChangeDate { get; set; }

    public int GoodsPart { get; set; }

    public Goods(int goodsCode, int goodsPrice, int changeDate, int goodsPart)
    {
        GoodsCode = goodsCode;
        GoodsPrice = goodsPrice;
        ChangeDate = changeDate;
        GoodsPart = goodsPart;
    }
}

var goods = new List<Goods>
{
    new Goods(8800, 1000, 20220911, 1),
    new Goods(8800, 1200,  20220914, 1),
    new Goods(8801, 1300, 20220928, 2),
    new Goods(8801, 1500, 20220929, 2),
    new Goods(8802, 1300, 20220916, 3),
    new Goods(8802, 1500, 20221001, 3),
    new Goods(8802, 2400, 20221004, 3)
};

var eachMax = goods.GroupBy(x => x.GoodsCode).Select(x => x.MaxBy(y => y.ChangeDate));
eachMax.ToList().ForEach(x => 
    Console.WriteLine($"{x.GoodsCode} {x.GoodsPrice} {x.ChangeDate} {x.GoodsPart}"));

// 8800 1200 20220914 1
// 8801 1500 20220929 2
// 8802 2400 20221004 3
  • Related