Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Getting Distinct Values 1

Status
Not open for further replies.
BG12424

BG12424

Programmer
Jun 4, 2002
717
US
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
 
Sort by date Sort by votes
Can you not just use a DISTINCT statement in your SQL?
 
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
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.


Code: 
        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
 
Upvote 0 Downvote
Brain -

An impressive loop, great solution. The idea of looping throught a dataset was in the back of my mind. Nice job.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

stinkybee
  • Locked
  • Question
getting DropDownList Value from ListView InsertItemTemlate
Replies
1
Views
358
jbenson001
jbenson001
Dashley
  • Locked
  • Question
Client Script Changing/encoding Values 1
Replies
2
Views
219
Dashley
Dashley
JoeCool32
  • Locked
  • Question
Getting old .NET pages ready for today's web
Replies
0
Views
207
JoeCool32
JoeCool32
dvannoy
  • Locked
  • Question
Getting Value from Last Record in DB 1
Replies
4
Views
237
jbenson001
jbenson001
JRB-Bldr
  • Locked
  • Question
Pass Values Back from Running Thread to Primary Server Code (VB.Net)
Replies
1
Views
222
JRB-Bldr
JRB-Bldr

Part and Inventory Search

Sponsor

Back
Top