How to select distinct from a dataset/dataview ?

T

trend

Well-known member
Joined
Oct 12, 2004
Messages
171
Hello, I need to select distinct rows from a dataset..

I have seen this posting:

http://www.computerhelp.forum/showthread.php?t=74539&highlight=distinct+dataview

http://support.microsoft.com/default.aspx?scid=kb;EN-US;325684

and
Code: 
Public Function SelectDistinct(ByVal TableName As String, _
                               ByVal SourceTable As DataTable, _
                               ByVal FieldName As String) As DataTable
    Dim dt As New DataTable(TableName)
    dt.Columns.Add(FieldName, SourceTable.Columns(FieldName).DataType)
    Dim dr As DataRow, LastValue As Object
    For Each dr In SourceTable.Select("", FieldName)
        If LastValue Is Nothing OrElse Not ColumnEqual(LastValue, dr(FieldName)) Then
            LastValue = dr(FieldName)
            dt.Rows.Add(New Object() {LastValue})
        End If
    Next
    If Not ds Is Nothing Then ds.Tables.Add(dt)
    Return dt
End Function

but this just seems like a long way of doing something that should be simple.. even the above code could be simplier... If i have to move through one dataset and create another.. couldnt I just do something like: dataset.Unique = True


Do youll have any suggestions?

thanks!
Lee
 
Actually.. It looks like the MS code above will search for a word and get a distinct result.. But I need to query a table and get all the distinct results
 
Actually.. I think (?) I just found some C code to do what i want.. am I right.. and any C people out there help me pseudo code it :)

Code: 
DataTable PurgedDataTable = _DataTable.Clone();
                DataColumn []_Columns = new DataColumn[PurgedDataTable.Columns.Count];
                for (int i =0 ; i< _DataTable.Columns.Count ; i++)
                {
                    _Columns[i]= PurgedDataTable.Columns[i];
                }
                UniqueConstraint _UniqueConstraint = new UniqueConstraint(_Columns);
                
                PurgedDataTable.Constraints.Add(_UniqueConstraint);

                for (int i =0 ; i< _DataTable.Rows.Count ; i++)
                {
                    try
                    {
                        PurgedDataTable.ImportRow(_DataTable.Rows[i]);
                    }
                    catch(Exception ex)
                    {
                        // Keep quite
                    }
                }
 
ahh ok.. I found some code that almost works :)

Code: 
Public Function DistinctRows_Int32_Key(ByVal dt As DataTable, ByVal keyfield As String) As DataTable
  Dim newTable As DataTable = dt.Clone
  Dim keyval As Int32 = 0
  Dim dv As DataView = dt.DefaultView
  dv.Sort = keyfield
  If dt.Rows.Count > 0 Then
    For Each dr As DataRow In dt.Rows
      If Not dr.Item(keyfield) = keyval Then
        newTable.ImportRow(dr)
        keyval = dr.Item(keyfield)
      End If
    Next
  Else
    newTable = dt.Clone
  End If
  Return newTable
End Function

the only issue is that if the column has data like this:

Dog
Cat
Dog
Dog
Dog
Dog
Bear
Rat

you will get outputlike:
Dog
Cat
Dog
Bear
Rat


Maybe if I can sort the dataset from A-Z first.. this will fix the issue.. how can i do this? :)
 
Back
Top