[RESOLVED] Sorting a datatable out?
All,
I have a problem which I am struggling to efficiently put into code. I have a datatable
ID | NAME | DATE | JOB
0 | Joe | 01/01 | Selling
1 | Bob | 03/01 | Buying
2 | Joe | 02/01 | Manager
3 | John | 01/01 | Buying
How can i programmatically get it to only show a unique list of names and the if there is a duplicate name to only show the latest entry, ie:
ID | NAME | DATE | JOB
1 | Bob | 03/01 | Buying
2 | Joe | 02/01 | Manager
3 | John | 01/01 | Buying
Anyone have any ideas?
Cheers,
Ken
Re: Sorting a datatable out?
"...get it to only show..."
Do you mean you want a DataView representing the filtered rows? Or do you want to run a query on the DataTable (LINQ) to return the specified rows?
Re: Sorting a datatable out?
I just want to put it in another datatable.
Re: Sorting a datatable out?
Latest entry based on date right?
vb.net Code:
Public Class Form1
Public Sub New()
Me.InitializeComponent()
Dim table As New DataTable()
With table
With .Columns
.Add("ID", GetType(Integer))
.Add("Name", GetType(String))
.Add("Date", GetType(Date))
.Add("Job", GetType(String))
End With
With .Rows
.Add(0, "Joe", #1/1/2010#, "Selling")
.Add(1, "Bob", #1/1/2010#, "Buying")
.Add(2, "Joe", #2/1/2010#, "Manager")
.Add(3, "John", #1/1/2010#, "Buying")
End With
End With
Dim results = table.AsEnumerable().OrderBy(Function(row) row.Field(Of Date)("Date")) _
.GroupBy(Function(row) row.Field(Of String)("Name")) _
.Select(Function(grouping) grouping.Last()).CopyToDataTable()
Me.DataGridView1.DataSource = results
End Sub
End Class
Re: Sorting a datatable out?
Ahhhh just saw that it was VB2005!! Disregard my post...
Re: Sorting a datatable out?
Bugger, can it not be done in VS2005?
:(
Re: Sorting a datatable out?
Quote:
Originally Posted by
dinosaur_uk
Bugger, can it not be done in VS2005?
:(
Not with LINQ... Upgrade? :D
Kidding, I'll think about the logic for VB2005.
Re: Sorting a datatable out?
Thanks dude,
my code at the moment is crazily long....and it still doesnt work!
Re: Sorting a datatable out?
Quote:
Originally Posted by
dinosaur_uk
Thanks dude,
my code at the moment is crazily long....and it still doesnt work!
Try this:
vb.net Code:
Public Class Form1
Public Sub New()
Me.InitializeComponent()
Dim table As New DataTable()
Dim tableResults As DataTable = Nothing
Dim rowResults As New List(Of DataRow)()
Dim names As New List(Of String)()
'Create structure
With table
With .Columns
.Add("ID", GetType(Integer))
.Add("NAME", GetType(String))
.Add("DATE", GetType(Date))
.Add("JOB", GetType(String))
End With
With .Rows
.Add(0, "Joe", #1/1/2010#, "Selling")
.Add(1, "Bob", #1/1/2010#, "Buying")
.Add(2, "Joe", #2/1/2010#, "Manager")
.Add(3, "John", #1/1/2010#, "Buying")
.Add(4, "John", #1/3/2010#, "Buying")
End With
End With
'Clone table structure
tableResults = table.Clone()
'Get unique names
For Each row As DataRow In table.Rows
If Not names.Contains(row("NAME").ToString()) Then
names.Add(row("NAME").ToString())
End If
Next
'Get latest entry
For Each name As String In names
Dim maxDate As Date = Date.MinValue
Dim maxDateRow As DataRow = Nothing
table.DefaultView.RowFilter = String.Format("[NAME] LIKE '{0}'", name)
For Each row As DataRowView In table.DefaultView
Dim [date] As Date = CDate(row("DATE"))
If [date] > maxDate Then
maxDate = [date]
maxDateRow = row.Row
End If
Next
rowResults.Add(maxDateRow)
Next
'Add results to a new DataTable
For Each row As DataRow In rowResults
Dim newRow As DataRow = tableResults.NewRow()
newRow.ItemArray = row.ItemArray
tableResults.Rows.Add(newRow)
Next
'Set DataSource
Me.DataGridView1.DataSource = tableResults
End Sub
End Class
Re: Sorting a datatable out?
This is brilliant! It works!
I need to work this out in my head now....
Re: [RESOLVED] Sorting a datatable out?
Is there a way of doing this in SQL query?