Getting Distinct Values 1

[BG12424]

I am having trouble getting distinct values from a column in a datatable. The column is a list of email addresses,which can have multiple line items with the same email address. The problem is that this colum is not a primary key and is the only column I need to return. I would like to get the count of the distinct email addresses in this datatable, but I am not able to succeed at it.

I have tried copying a dataset, applying primary keys to original dataset, then merging the new and original datasets together, but I'm not successful because of my duplicate email addresses. HOW CAN I APPROACH THIS, PLEASE HELP? THANKS


regards,
Brian

http://www.gainesconsulting.com

 

[Isadore]

Can you not just use a DISTINCT statement in your SQL?

 

[BG12424]

This would be an obvious approach, but my results does not warrant me this capability since I have a composite key of 2 fields that comprise my primary key. Once I get these results, I then need to break it apart and get the unique emails as part of the resultset that is being returned and displayed in my datagrid. Thanks


regards,
Brian

http://www.gainesconsulting.com

 

[Isadore]

bg - my apoligies. It was very late, sure that was obvious enough.

Perhaps nested grids may provide an insight here - seems like whatever approach you take you'll have to refilter the fist recordset returned (also, cd consider a nested SQL statement). If anything pops up, I'll get back with you, Good Luck bg.

 

[BG12424]

Thanks.


regards,
Brian

http://www.gainesconsulting.com

 

[bboffin]

Try "SELECT COUNT(DISTINCT emailaddress) AS CountOfAddresses FROM MyTable"

This should do exactly what you want and will be very efficient.

FYI the DISTINCT keyword has nothing to do with primary keys. All it does is eliminate duplicate rows from the results.

 

[BG12424]

Thank you, but my problem does not reside in the SQL coming from the database. It has to do with the data already in DataTables and getting distinct values from them. I used the following code to get what I was looking for.

        For i = dsNew.Tables("Firms").Rows.Count - 1 To 0 Step -1
            expr = "NFA_ID = '" & dsNew.Tables("Firms").Rows(i).Item(0) & "'"
            foundRows = dsNew.Tables("Firms").Select(expr)
            If foundRows.GetLength(0) > 1 Or dsNew.Tables("Firms").Rows(i).Item(0) = "" Then
                dsNew.Tables("Firms").Rows.RemoveAt(i)
                dsNew.Tables("Firms").AcceptChanges()
            End If
        Next i
        Response.Write(dsNew.Tables("Firms").Rows.Count)

regards,
Brian

http://www.gainesconsulting.com

 

[Isadore]

Brain -

An impressive loop, great solution. The idea of looping throught a dataset was in the back of my mind. Nice job.

 

[BG12424]

Thanks for the appreciation.


regards,
Brian

http://www.gainesconsulting.com