Remove Data Row with value less than other row

Question

I have a Data Table with multiple row with the same "IPC" values but different "Security" values.

For every "IPC" I'm interested at the rows that have the highest Mkt Value and delete the others.

Here an example:enter image description here

How can i do this in C# with a simple query? Thank you.

jdweng · Accepted Answer · 2020-07-31T15:15:42.290

Try following :

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
namespace ConsoleApplication11
{
    class Program
    {
        static void Main(string[] args)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("IPC", typeof(long));
            dt.Columns.Add("Issuer", typeof(string));
            dt.Columns.Add("Sector", typeof(string));
            dt.Columns.Add("Mkt Value", typeof(long));
            dt.Columns.Add("Security", typeof(string));

            dt.Rows.Add(new object[] { 112345, "Ipsen", "Senior", 10910505, "AA" });
            dt.Rows.Add(new object[] { 112345, "Ipsen", "Senior", 738427911, "AAA" });
            dt.Rows.Add(new object[] { 112345, "Ipsen", "Senior", 51467951, "A" });
            dt.Rows.Add(new object[] { 54321, "Legrand", "Hybrid", 485017008, "BBB" });
            dt.Rows.Add(new object[] { 54321, "Legrand", "Hybrid", 28949439, "B+" });
            dt.Rows.Add(new object[] { 54321, "Legrand", "Hybrid", 1299024462, "BB" });

            var maxIPC = dt.AsEnumerable()
                .OrderByDescending(x => x.Field<long>("Mkt Value"))
                .GroupBy(x => x.Field<long>("IPC"))
                .Select(x => new { row = x.FirstOrDefault(), sum = x.Sum(y => y.Field<long>("Mkt Value")) });

            foreach (var row in maxIPC)
            {
                row.row["Mkt Value"] = row.sum;
            }

            DataTable maxIPCtable = maxIPC.Select(x => x.row).CopyToDataTable();

        }
    }
}

Remove Data Row with value less than other row

1 Answers1