LINQ select first row in each group

Question

This is sample data

id | customer | total
---+----------+------
1 | Joe | 5
2 | Sally | 3
3 | Joe | 2
4 | Sally | 1

i will select those customer name which has maximum value

in SQL i can do it this way

Approach 1

select * from customer c1
where Total = 
(
    select max(total) from customer c2
    where c1.customer=c2.customer
    group by c2.customer
)
order by total

Approach 2

SELECT *
FROM (
SELECT ROW_NUMBER()
OVER(PARTITION BY customer
ORDER BY total DESC) AS StRank, *
FROM customer) n
WHERE StRank = 1

How to achieve the same with LINQ query if data is in List<T> ? thanks

Answer 1

Hello @Sudip Bhatt

See if this will give you what you are looking for using C#9

using System;  
using System.Collections.Generic;  
using System.Linq;  
  
namespace ConsoleApp1  
{  
    class Program  
    {  
        static void Main(string[] args)  
        {  
            var results = Mocked()  
                .GroupBy(person => person.Customer)   
                .OrderByDescending(group => group.Max(person => person.Total))  
                .Select(group => group.OrderBy(person => person.Total))  
                .Select((people,index) => new {RowIndex = index +1, item = people})  
                .ToList();  
  
            foreach (var result in results)  
            {  
                Console.WriteLine($"{result.RowIndex}, {result.item.LastOrDefault()}");  
            }  
  
  
            Console.ReadLine();  
        }  
  
        static List<Person> Mocked()  
        {  
            var list = new List<Person>  
            {  
                new() {Id = 1, Customer = "Sally", Total = 1},  
                new() {Id = 2, Customer = "Joe", Total = 2},  
                new() {Id = 3, Customer = "Bill", Total = 5},  
                new() {Id = 4, Customer = "Sally", Total = 3},  
                new() {Id = 5, Customer = "Joe", Total = 6}  
            };  
  
            return list;  
        }  
    }  
  
     
    public class Person  
    {  
        public int Id { get; set; }  
        public string Customer { get; set; }  
        public int Total { get; set; }  
        public override string ToString()  
        {  
            return $"{Customer},{Total}";  
        }  
    }  
}