[RESOLVED] Sorting a datatable out?

[dinosaur_uk]

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

[ForumAccount]

"...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?

[dinosaur_uk]

I just want to put it in another datatable.

[ForumAccount]

Latest entry based on date right?

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

[ForumAccount]

Ahhhh just saw that it was VB2005!! Disregard my post...

[dinosaur_uk]

Bugger, can it not be done in VS2005?

[ForumAccount]

Bugger, can it not be done in VS2005?

Not with LINQ... Upgrade?

Kidding, I'll think about the logic for VB2005.

[dinosaur_uk]

Thanks dude,

my code at the moment is crazily long....and it still doesnt work!

[ForumAccount]

Thanks dude,

my code at the moment is crazily long....and it still doesnt work!

Try this:

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

[dinosaur_uk]

This is brilliant! It works!

I need to work this out in my head now....

[dinosaur_uk]

Is there a way of doing this in SQL query?