Vishwamohan

These are 2 samples of  Group By Examples, one is very simple one but not simplest, second one is little bit intermediate. 

First creating a class to use in the given example

   1: Public Class TestRecord

   2:     Property ID As String

   3:     Property Value As String

   4:     Property RecordDate As Date

   5:  

   6:     Sub New()

   7:  

   8:     End Sub

   9:  

  10:     Sub New(ByVal id As String, ByVal value As String, ByVal recDate As Date)

  11:         Me.ID = id

  12:         Me.Value = value

  13:         Me.RecordDate = recDate

  14:     End Sub

  15: End Class

 

Now, let say use a button and Gridview to present the data on a page.

   1: Protected Sub Button4_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button4.Click

   2:         Dim showSimpleGroup As Boolean = False

   3:  

   4:         Dim lstItems As New List(Of TestRecord)

   5:         lstItems.Add(New TestRecord("1", "100", Now))

   6:         lstItems.Add(New TestRecord("2", "100", Date.MinValue))

   7:         lstItems.Add(New TestRecord("3", "100", #2/2/1980#))

   8:         lstItems.Add(New TestRecord("4", "200", Date.MinValue))

   9:         lstItems.Add(New TestRecord("5", "200", #1/1/2010#))

  10:         lstItems.Add(New TestRecord("6", "300", Now))

  11:  

  12:         If showSimpleGroup Then

  13:             Dim recList = From r In lstItems Group By r.Value Into g = Group Select New With {g, .ID = g.Max(Function(p) p.ID), .Value = g.Max(Function(p) p.Value)}

  14:             Me.gvwTest.DataSource = recList

  15:             Me.gvwTest.DataBind()

  16:         Else

  17:             Dim tempList = From d As TestRecord In lstItems Group By d.Value Into g = Group

  18:                             Select New With {g, .ID = g.Max(Function(d) d.ID), .RecordDate = g.Max(Function(d) IIf(d.RecordDate = Date.MinValue, Now, d.RecordDate))}

  19:  

  20:             If tempList IsNot Nothing AndAlso tempList.Count > 0 Then

  21:                 Dim recList = From r As TestRecord In lstItems Join t In tempList On t.ID Equals r.ID Select r

  22:                 Me.gvwTest.DataSource = recList

  23:                 Me.gvwTest.DataBind()

  24:             End If

  25:         End If

  26:     End Sub

 

The first grouping will display the distinct value excluding Record Date , second one will use the record’s max date to display the record the distinct value.