How to remove duplicate rows from a datatable based on two columns? (c# or vb?)

tb303 · Jan 1, 2013

Hi BHW,

I have a data table with columns "name","phone","login","password" as columns

I want to remove all the rows where "name" & "phone" are duplicated.

But im having a hard time coming up with a google search to find my answer.

I know I can do it based on one column like this...

Code:

Dim primaryKey(1) As DataColumnprimaryKey(0) = newdataset.Tables("tablename").Columns("name")
newdataset.Tables("tablename").PrimaryKey = primaryKey
newdataset.Tables("tablename").Merge(originaldataset.Tables("tablename"), True) 'merge in unique only
newdataset.Tables("tablename").PrimaryKey = Nothing

but that doesnt work if you have two people with the same name but different phone numbers.

Any one point me in the right direction?

zyx2000 · Jan 4, 2013

You could do this straight in SQL with a subquery.

BlackHatWhiteHearth · Jan 5, 2013

It is not very clear what you are trying to achieve. How to handle the login and password columns ?
John 123456 and John 123456 - obvious diplicates
John 654321 and John 123456 - duplicates or not ? People with duplicated names, doesn't necessarily mean that they are the same person.

John 123456 username1 password1 and John 123456 username2 password2 - duplicates, but the person has two accounts, which of the accounts should be deleted ?

tb303 · Jan 5, 2013

im sorry maybe it was a bad example - i rushed my post.

Another example is a proxychecker.
So it scrapes and imports proxys with various columns to the data table where the proxy address and port are in two columns on the datatable called "port" and "proxy"

so this list....

Code:

52.20.10.92:8888
52.19.99.30:3128
52.20.10.92:8888
68.82.77.20:8000
68.82.77.20:8888

becomes...

Code:

52.20.10.92:8888
52.19.99.30:3128
68.82.77.20:8000
68.82.77.20:8888

currently what im doing as a work around for this is taking the "proxy" & "port" fields joining them back together with at ":" to make a string for each proxy.
then adding those strings to a new list of strings using distinct. Then parsing that list to split the proxy string back into proxy / port and adding that to a new datatable.
but that seems a very long way round!

SQL i guess as posted above would do it but I cant find an example.

cgimaster · Jan 5, 2013

http://www.dotnetspider.com/resources/4535-Remove-duplicate-records-from-table.aspx

In the above example it shows how to do for 1 column but adding another one on the check would be fairly easy so I think you can pick it up from there.

BlackHatWhiteHearth · Jan 5, 2013

Use the GROUP BY clause:

Code:

using (SqlConnection connection = new SqlConnection("your connection string goes here"))            
using (SqlCommand command = new SqlCommand("SELECT [IP], [Port] FROM TableName GROUP BY IP, Port", connection))
{
  connection.Open();
  using (SqlDataReader reader = command.ExecuteReader())
  {
    while (reader.Read())
    {
       string ip = reader.GetString(0);
       string port = reader.GetString(1);
       //do whatever is needed
    }
  }
  connection.Close();
}

The code is in C#

tb303 · Jan 6, 2013

thanks, that pointed me in the right direction

BlackHatWhiteHearth · Jan 6, 2013

You can also use:

Code:

SELECT [IP], MAX([Port]) FROM TableName GROUP BY IP

assuming [Port] is a numeric value(integer)
this will eliminate the duplicated IPs and will return the IPs with the largest [Port]
For more information look for "SQL Aggregate Functions"
or

Code:

SELECT DISTINCT [IP], [Port] FROM TableName

which will produce the same results as grouping by two columns.

How to remove duplicate rows from a datatable based on two columns? (c# or vb?)

tb303

Supreme Member

zyx2000

Registered Member

BlackHatWhiteHearth

BANNED

tb303

Supreme Member

cgimaster

Power Member

BlackHatWhiteHearth

BANNED

tb303

Supreme Member

BlackHatWhiteHearth

BANNED

Main Menu

Marketplace

Making Money

BlackHat World