Dataview, Dataset - Select Distinct

[Dianna]

I don't understand why the dataview or the dataset does not have a 'Select Distinct' option. They both can do so much to filter the data except that. The article Microsoft has 326176 works, but not if you need to select distinct on multiple fields.

Does anyone have any suggestions

Thanks
Diann

[William Ryan eMVP]

Dianna:

What is the SQL Equivalent that you are thinking of writing? If you are
trying return distinct values of the combination of say two columns for
instance, you can do a sort by the first field then the secondand then just
walk through it from there. Agreed it's not elegant but a datatable isn't
a full featured RDBMS system. They are pretty darned powerful so I know
it's easy to expect such things, but if you need some really complex
filtering, you may just want to make a trip to the DB with a DataReader.

HTH,

Bill
"Dianna" <an*******@discussions.microsoft.com> wrote in message
news:3A**********************************@microsof t.com...

I don't understand why the dataview or the dataset does not have a 'Select Distinct' option. They both can do so much to filter the data except that.
The article Microsoft has 326176 works, but not if you need to select
distinct on multiple fields.
Does anyone have any suggestions?

Thanks,
Dianna

[Dianna]

Might I ask how that would look

dt.Columns.Add(FieldName1, SourceTable.Columns(FieldName1).DataType
dt.Columns.Add(FieldName2, SourceTable.Columns(FieldName2).DataType
dt.Columns.Add(FieldName3, SourceTable.Columns(FieldName3).DataType
dt.Columns.Add(FieldName4, SourceTable.Columns(FieldName4).DataType

Dim dr As DataRow, LastValue As Objec
For Each dr In SourceTable.Select("", FieldName1
If LastValue Is Nothing OrElse Not ColumnEqual(LastValue, dr(FieldName1)) The
LastValue = dr(FieldName1
dt.Rows.Add(New Object() {LastValue}
End I
Nex

If Not ds Is Nothing Then ds.Tables.Add(dt

'call to function
dsHelper.SelectDistinct("DistinctShows", ds.Tables!ShowNo, "show_name", "dow", "start_time", "end_time"

I'm wondering if it might be easier to just sort the table and just write an old fashion control break. It might be more elegant
Thanks
Diann

[Cor]

Hi Dianna,

I'm wondering if it might be easier to just sort the table and just write

an old fashion control >break. It might be more elegant.
I got this idea from your sentence, it is quick made, but I think that it is
generic usable.

I hope this helps a little bit?

Cor
\\\
Me.DataGrid1.DataSource = mydistinct.distinct(dt, "elem1")
///
\\\
Public Class Selectclass
Public Function distinct(ByVal dt As DataTable, _
ByVal dist As String) As DataTable
Dim dtclone As DataTable = dt.Clone
Dim dv As New DataView(dt)
dv.Sort = dist
Dim myselold As String = ""
For i As Integer = 0 To dv.Count - 1
If myselold <> dv(i)(dist).ToString Then
Dim drn As DataRow = dtclone.NewRow
For y As Integer = 0 To dv.Count - 1
drn(y) = dv(i)(y)
Next
myselold = dv(i)(dist).ToString
dtclone.Rows.Add(drn)
End If
Next
Return dtclone
End Function
End Class
///

[Dianna]

Hi Cor

Thats sort of what I did (kind of had no choice).

I am sure in the future their will be a 'Select Distict' for the tables.

Thanks
Diann

[Cor]

Hi Dianna,

I'm wondering if it might be easier to just sort the table and just write

an old fashion control >break. It might be more elegant.
I got this idea from your sentence, it is quick made, but I think that it is
generic usable.

I hope this helps a little bit?

Cor
\\\
Me.DataGrid1.DataSource = mydistinct.distinct(dt, "elem1")
///
\\\
Public Class Selectclass
Public Function distinct(ByVal dt As DataTable, _
ByVal dist As String) As DataTable
Dim dtclone As DataTable = dt.Clone
Dim dv As New DataView(dt)
dv.Sort = dist
Dim myselold As String = ""
For i As Integer = 0 To dv.Count - 1
If myselold <> dv(i)(dist).ToString Then
Dim drn As DataRow = dtclone.NewRow
For y As Integer = 0 To dv.Count - 1
drn(y) = dv(i)(y)
Next
myselold = dv(i)(dist).ToString
dtclone.Rows.Add(drn)
End If
Next
Return dtclone
End Function
End Class
///

[Dianna]

Hi Cor

Thats sort of what I did (kind of had no choice).

I am sure in the future their will be a 'Select Distict' for the tables.

Thanks
Diann