Filtering DataTable in VB.Net

Question

I am having a datatable with following structure.

mid    mname      version  baseID
------------------------------------
1      tag3         1       1
2      tag3         2       1
3      tag3         3       1
4      tag5         1       4
5      tag5         2       4 
6      tag6         1       6

From the above structure I need a datatable similar like this. I need to take the highest version from the datatable.

mid    mname      version  baseID
------------------------------------
3      tag3         3       1
5      tag5         2       4 
6      tag6         1       6

How can I create a datatable similar like the above. I am using Vb.Net

Thanks in advance.

I cannot modify the query.(SQL Part) – Coderr Sep 29 '11 at 05:17 — Coderr, Sep 29 '11 at 05:17

Chris Santiago · Answer 1 · 2011-09-28T15:52:14.030

It depends upon how your populating the original datatable. if you are doing it by querying a database I would format the result set in the query itself something like:

(SQL)

select mid, mname, version, baseID
from <table> t
Join (select baseid, max(version) as version 
         from <table> group by baseID)maxVersion 
    on maxVersion.baseid = t.baseid and maxVersion.version = t.version

this would format the data before its put into the datatable.

If the datatable is populated by other means and you need to filter it then put it in antoher datatable. then an easy way to do it (assuming your datatable is always sorted in the above manner) is:

for i as integer = 0 to datatable.rows.count - 1
    if i < datatable.rows.count - 1 Then
        if datatable.rows(i)(4).value <> datatable.rows(i+1)(4).value then
            newDataTable.rows.Add(datatable.rows(i).itemArray())
        End If
    Else
        newDataTable.rows.Add(datatable.rows(i).itemArray())
    End if
Next

Now the syntax might be off here or there (wrote it in notpad) but the idea of the logic is there. assuming you have the same format each time then I'm checking for when the row changes to a new baseid and adding that row to the new datatable. from the example you posted it looks like the max version of a particular baseid is the last row of that baseid.

-Chris

The solution you provided is not working. Sory to disappoint you :( — Coderr, Sep 29 '11 at 10:21

score 0 · Accepted Answer · answered Sep 29 '11 at 10:20

I have used

Datatables.Compute() to get the MAX value using GROUP By

Function GroupBy(ByVal i_sGroupByColumn As String, ByVal i_sAggregateColumn As String, ByVal i_dSourceTable As DataTable) As DataTable

        Dim dv As New DataView(i_dSourceTable)

        'getting distinct values for group column
        Dim dtGroup As DataTable = dv.ToTable(True, New String() {i_sGroupByColumn})
        'adding column for the row count
        dtGroup.Columns.Add("Max_Version", GetType(Double))

        'looping thru distinct values for the group, counting
        For Each dr As DataRow In dtGroup.Rows
            dr("Max_Version") = i_dSourceTable.Compute("MAX(" & i_sAggregateColumn & ")", i_sGroupByColumn & " = '" & dr(i_sGroupByColumn) & "'")

        Next

        'returning grouped/counted result
        Return dtGroup
    End Function

Filtering DataTable in VB.Net

2 Answers2