max and group by question with LINQ

Question

I want to group the below query by GetSetDomainName and select the row which has the maximum GetSetKalanGun.In other words, I am trying to get the row with the maximum KALANGUN among those which have the same DOMAINNAME.

var kayitlar3 = (
    from rows in islemDetayKayitListesi
    select new
    {
        KAYITNO = rows.GetSetKayitNo,
        HESAPADI = rows.GetSetHesapAdi,
        URUNNO = rows.GetSetUrunNo,
        URUNADI = rows.GetSetUrunAdi,
        URUNMIKTAR = rows.GetSetUrunMiktar,

        ISLEMTARIHI = rows.GetSetIslemTarihi,
        HIZMETDURUMU = rows.GetSetHizmetDurumu,
        TOPLAMTUTAR = rows.GetSetToplamTutar,

        HIZMETBASLANGICTARIHI = rows.GetSetHizmetBaslangicTarihi,
        HIZMETBITISTARIHI = rows.GetSetHizmetBitisTarihi,
        KALANGUN = rows.GetSetKalanGun 
        DOMAINNAME = rows.GetSetDomainName,
        SIPARISDURUMU = rows.GetSetSiparisDurumu
    }).AsQueryable();

This is what I get

KAYITNO DOMAINNAME KALANGUN
1       asdf.com      30
2       domnam.com    172
3       asdf.com      40
4       xyz.com       350

This is what I want

KAYITNO DOMAINNAME KALANGUN
 2       domnam.com    172
 3       asdf.com      40
 4       xyz.com       350

var islemDetayKayitListesi = new List<IslemDetayKayit>();

islemDetayKayitListesi get filled with a foreach loop, with no problem

And that is what IslemDetayKayit looks like

public class IslemDetayKayit
{
    public int GetSetKayitNo { get; set; }
    public string GetSetHesapAdi { get; set; }
    public string GetSetUrunNo { get; set; }
    public string GetSetUrunAdi { get; set; }
    public double GetSetUrunMiktar { get; set; }
    public string GetSetIslemTarihi { get; set; }
    public string GetSetHizmetDurumu { get; set; }
    public string GetSetToplamTutar { get; set; }
    public string GetSetHizmetBaslangicTarihi { get; set; }
    public string GetSetHizmetBitisTarihi { get; set; }
    public int GetSetKalanGun { get; set; }
    public string GetSetSiparisDurumu { get; set; }
    public string GetSetDomainName { get; set; }
}

EDIT : I figured out that there was some other problem in my code, and corrected it.After that all the answer I had to this question works.Thank you for helping and teaching me new things.

Wouldn't the result row be the same whether you grouped or not? The maximum won't change. — , Jun 06 '11 at 14:39
@BurnAfterReading: you're going to have to elaborate further as your sample output doesn't quite make sense. Do you want to skip ALL rows until you reach the maximal row and then output the rest? — user7116, Jun 06 '11 at 14:53
@Steven I needed that before some changes Mr.Steven it didn't do any harm yet, and I tried everything after I removed and nothing changed, too. — Bastardo, Jun 06 '11 at 15:45
@BurnAfterReading: Converting an in-memory enumerable to an queryable is hardly ever a solution. It will only slow down the execution enormously. The only useful scenario I've seen is for unit testing to mimic a LINQ provider. So if it isn't there for a special reason, loose the `AsQueryable`. — Steven, Jun 06 '11 at 16:59
@BurnAfterReading: You might need to work on your language skills. Take a look at how I refactored your code. Please loose the `GetSet` prefix on the properties. It doesn't add anything. As a matter of fact; it just makes it harder to read the code. — Steven, Jun 06 '11 at 17:05

Steven · Answer 1 · 2011-06-06T15:35:17.627

5

This will do the trick:

var q =
    from item in kayitlar3
    group item by item.DOMAINNAME into g
    select g.OrderByDescending(i => i.KALANGUN).First();

You can also try this:

var q = 
    from row in islemDetayKayitListesi
    group row by row.GetSetDomainName into g
    let highest = g.OrderByDescending(r => r.GetSetKalanGun).First()
    select new
    {
        KAYITNO = highest.GetSetKayitNo,
        DOMAINNAME = g.Key,
        KALANGUN = highest.GetSetKalanGun
    };

Note that this would yield the same results. If it doesn't, there is a problem with your code that we can't see by looking at the information that you posted.

edited Jun 06 '11 at 15:35

answered Jun 06 '11 at 14:41

Steven

166,672
24
332
435

yes, good thinking Mr.Steven.However, not the result I wanted I guess I am not making my point clear.I will just show some output and my desired output.thank you very much. – Bastardo Jun 06 '11 at 14:45
I added some details about the expected output @Mr.Steven, could you give me any ideas with that? – Bastardo Jun 06 '11 at 14:51
1

@BurnAfterReading: After reading your update, my answer still stands. It does give the desired output. The `q` variable contains a list of anonymous types where each `DOMAINNAME` is unique and contains the item with the highest `KALANGUN` among items with the same `DOMAINNAME`. This is exactly what you are asking and what you are showing in your update. – Steven Jun 06 '11 at 15:19
However the output I get is `KAYITNO DOMAINNAME KALANGUN 4 xyz.com 350` – Bastardo Jun 06 '11 at 15:24
thanks for your replys, I will try these and check other parts of my code. – Bastardo Jun 06 '11 at 15:46

FlyingStreudel · Accepted Answer · 2011-06-06T15:45:14.430

You could use:

var kayitlar3 = 
    islemDetayKayitListesi.
    Select(rows => 
    new
    {
        KAYITNO = rows.GetSetKayitNo,
        HESAPADI = rows.GetSetHesapAdi,
        URUNNO = rows.GetSetUrunNo,
        URUNADI = rows.GetSetUrunAdi,
        URUNMIKTAR = rows.GetSetUrunMiktar,

        ISLEMTARIHI = rows.GetSetIslemTarihi,
        HIZMETDURUMU = rows.GetSetHizmetDurumu,
        TOPLAMTUTAR = rows.GetSetToplamTutar,

        HIZMETBASLANGICTARIHI = rows.GetSetHizmetBaslangicTarihi,
        HIZMETBITISTARIHI = rows.GetSetHizmetBitisTarihi,
        KALANGUN = rows.GetSetKalanGun,
        DOMAINNAME = rows.GetSetDomainName,
        SIPARISDURUMU = rows.GetSetSiparisDurumu
    }).
    GroupBy(a => 
        //To ignore case and trailing/leading whitespace
        a.DOMAINNAME.ToUpper().Trim()).
    Select(g => 
         g.OrderByDescending(a => a.KALANGUN).FirstOrDefault()).
    AsQueryable();

EDIT:

So using this code:

        List<Thing> islemDetayKayitListesi = new List<Thing>();
        Thing a = new Thing() { GetSetDomainName = "abc.com", GetSetKayitNo = 1,
            GetSetKalanGun = 40 };
        Thing b = new Thing() { GetSetDomainName = "abc.com", GetSetKayitNo = 2, 
            GetSetKalanGun = 300 };
        Thing c = new Thing() { GetSetDomainName = "xyz.com", GetSetKayitNo = 3, 
            GetSetKalanGun = 400 };
        Thing d = new Thing() { GetSetDomainName = "123.com", GetSetKayitNo = 4, 
            GetSetKalanGun = 124 };
        islemDetayKayitListesi.Add(a);
        islemDetayKayitListesi.Add(b);
        islemDetayKayitListesi.Add(c);
        islemDetayKayitListesi.Add(d);
        var kayitlar3 =
            islemDetayKayitListesi.
                Select(rows =>
                new
                {
                    KAYITNO = rows.GetSetKayitNo,
                    HESAPADI = rows.GetSetHesapAdi,
                    URUNNO = rows.GetSetUrunNo,
                    URUNADI = rows.GetSetUrunAdi,
                    URUNMIKTAR = rows.GetSetUrunMiktar,

                    ISLEMTARIHI = rows.GetSetIslemTarihi,
                    HIZMETDURUMU = rows.GetSetHizmetDurumu,
                    TOPLAMTUTAR = rows.GetSetToplamTutar,

                    HIZMETBASLANGICTARIHI = rows.GetSetHizmetBaslangicTarihi,
                    HIZMETBITISTARIHI = rows.GetSetHizmetBitisTarihi,
                    KALANGUN = rows.GetSetKalanGun,
                    DOMAINNAME = rows.GetSetDomainName,
                    SIPARISDURUMU = rows.GetSetSiparisDurumu
                }).
                GroupBy(anon =>
                    anon.DOMAINNAME).
                Select(g =>
                    g.OrderByDescending(anon => anon.KALANGUN).First()).
                AsQueryable();
        kayitlar3.ToList().
            ForEach(anon => Console.WriteLine("{0}, {1}, {2}", 
                anon.KAYITNO, anon.DOMAINNAME, anon.KALANGUN));

    struct Thing 
    {
        public int GetSetKayitNo { get; set; }
        public int GetSetHesapAdi { get; set; }
        public int GetSetUrunNo { get; set; }
        public int GetSetUrunAdi { get; set; }
        public int GetSetUrunMiktar { get; set; }

        public int GetSetIslemTarihi { get; set; }
        public int GetSetHizmetDurumu { get; set; }
        public int GetSetToplamTutar { get; set; }

        public int GetSetHizmetBaslangicTarihi { get; set; }
        public int GetSetHizmetBitisTarihi { get; set; }
        public int GetSetKalanGun { get; set; }
        public string GetSetDomainName { get; set; }
        public int GetSetSiparisDurumu { get; set; }
    }

I get the expected output:

2, abc.com, 300
3, xyz.com, 400
4, 123.com, 124

this is giving me to many error `islemDetayKayitListesi` is a list of `IslemDetayKayit`.thah might be the cause, and thanks. — Bastardo, Jun 06 '11 at 14:59
thanks for your interest Streudel, I hope you check the question update.This is giving the same result with Mr.Steven's answer. — Bastardo, Jun 06 '11 at 15:15
So it is printing two sections for asdf.com? Those strings would have to be different in order for that to occur. Is there perhaps trailing spaces or a case mismatch that would cause them to not compare as equal? — FlyingStreudel, Jun 06 '11 at 15:24
No I am only getting this output `KAYITNO DOMAINNAME KALANGUN 4 xyz.com 350 ` one row that has the max KALANGUN among all rows. — Bastardo, Jun 06 '11 at 15:27
Actually, I just tested it out, it totally works and returns all rows. — FlyingStreudel, Jun 06 '11 at 15:39
Interesting, I am still getting one row.Thank you so much, I will give it another shot. — Bastardo, Jun 06 '11 at 15:50

user7116 · Answer 3 · 2011-06-06T15:30:18.157

1

After clarification about your desired output, this will return the row with the top KALANGUN per DOMAINNAME:

var kayitlar3 = (
from rows in islemDetayKayitListesi
select new
{
    KAYITNO = rows.GetSetKayitNo,
    HESAPADI = rows.GetSetHesapAdi,
    URUNNO = rows.GetSetUrunNo,
    URUNADI = rows.GetSetUrunAdi,
    URUNMIKTAR = rows.GetSetUrunMiktar,

    ISLEMTARIHI = rows.GetSetIslemTarihi,
    HIZMETDURUMU = rows.GetSetHizmetDurumu,
    TOPLAMTUTAR = rows.GetSetToplamTutar,

    HIZMETBASLANGICTARIHI = rows.GetSetHizmetBaslangicTarihi,
    HIZMETBITISTARIHI = rows.GetSetHizmetBitisTarihi,
    KALANGUN = rows.GetSetKalanGun 
    DOMAINNAME = rows.GetSetDomainName,
    SIPARISDURUMU = rows.GetSetSiparisDurumu
})
.GroupBy(rr => rr.DOMAINNAME)
.SelectMany(gg => gg.OrderByDescending(rr => rr.KALANGUN).First());

edited Jun 06 '11 at 15:30

answered Jun 06 '11 at 15:01

user7116

63,008
17
141
172

Hım, thanks for the try,I guess I misdirected you in my comment Mr.sixlettervariables.I updated my desired output what I want to do is choosing the row with the maximum KALANGUN among those which have the same DOMAINNAME.That is why I thought I may need a group by. @sixlettervariables – Bastardo Jun 06 '11 at 15:10
Ok, so you want the maximum KALANGUN per DOMAINNAME. – user7116 Jun 06 '11 at 15:16
:D yes, thanks the row with the max KALANGUN per DOMAINNAME, I dont want to cause no more misunderstandings.Thanks for your help. – Bastardo Jun 06 '11 at 15:17
@BurnAfterReading: Edited to reflect your request. – user7116 Jun 06 '11 at 15:19
I have 11 errors with this version Mr.sixlettervariables one of them is `'System.Linq.IQueryable' does not contain a definition for 'Order' and no extension method 'Order' accepting a first argument of type 'System.Linq.IQueryable' could be found (are you missing a using directive or an assembly reference?)` – Bastardo Jun 06 '11 at 15:28
I removed `AsQueryable`, not sure why it is needed. – user7116 Jun 06 '11 at 15:30
Interesting, what type is `islemDetayKayitListesi`? – user7116 Jun 06 '11 at 15:41
it is a List `List islemDetayKayitListesi = new List();`. I will update the question. – Bastardo Jun 06 '11 at 15:42

sgmoore · Answer 4 · 2011-06-06T16:11:05.713

1

Try

  from rows in islemDetayKayitListesi 
  group rows by new { rows.GetSetDomainName} into results
  let MaxKALANGUN = results.Max(i=>i.KALANGUN)
  select new 
  { 
        KAYITNO = results.First(i=>i.KALANGUN== MaxKALANGUN).GetSetKayitNo
        DOMAINNAME = results.Key.GetSetDomainName , 
        KALANGUN = MaxKALANGUN
  }

If you want the complete class, try

  from rows in islemDetayKayitListesi 
  group rows by new { rows.GetSetDomainName} into results
  let MaxKALANGUN = results.Max(i=>i.KALANGUN)
  select results.First(i=>i.KALANGUN== MaxKALANGUN)

edited Jun 06 '11 at 16:11

answered Jun 06 '11 at 15:25

sgmoore

15,694
5
43
67

this will cause me to lose so many data even if this part works, I guess I can solve it with adding all values of islemDetayKayitListesi,well I need time thanks I will try it. – Bastardo Jun 06 '11 at 15:34
Thank you, I will try it as soon as it is possible and feedback. – Bastardo Jun 06 '11 at 18:28
Well it is again only one row just like the other answers with max KALANGUN.Thank you for trying. – Bastardo Jun 07 '11 at 07:13
Given the input you mention, this definitely returns three items, as does other solutions mentioned, so there must be some else wrong with your code. I would try creating a test solution maybe even using the code from FlyingStreudel to see if that works and then compare that with your code. – sgmoore Jun 07 '11 at 08:56
I edited my question sgmoore it works, actually all the answers work, thank you so much. – Bastardo Jun 07 '11 at 09:16

max and group by question with LINQ

4 Answers4