SQL query returning 6 lac records, remove duplicare record

nitin singh

7y807

My SQL query is returning around 6 lac records. From these records I have to remove rows which Is having repeated value for one of the columns : AccountID. I am iterating through the dataset in C# code using for loop and deleting records by checking value of the column AccountID if it already exists. This process is taking very long time. Pls suggest alternate ways to remove the duplicate records based on the value of column: AccountID. I tried below code to remove duplicate rows by column value:

public DataTable RemoveDuplicateRows(DataTable dTable, string colName)

{

Hashtable hTable = new Hashtable(); ArrayList duplicateList = new ArrayList(

);

foreach (DataRow drow in dTable.Rows)

{

if (hTable.Contains(drow[colName])) duplicateList.Add(drow);

else hTable.Add(drow[colName], string.Empty);

}

//Removing a list of duplicate items from datatable.

foreach (DataRow dRow in duplicateList)

dTable.Rows.Remove(dRow);

return dTable;

}

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sreekanth Reddy7y ago
Accepted answer
Hi Nitin,

Try the following :

SELECT *
FROM (SELECT AccountID,col1,col2,
ROW_NUMBER() OVER (PARTITION BY AccountID ORDER BY AccountID) AS RowNumber
FROM Table1
) AS a
WHERE a.RowNumber = 1
If it helps, Mark as answer.
Thank you.
Suraj Kumar7y ago
You can also try the following using Max. You can check the forum-

https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows
SELECT MyTable.* FROM (
SELECT MAX(AccountID) as AccountID, col1,col2 FROM MyTable Group By col1,col2
)a INNER JOIN MyTable ON a.AccountID = MyTable.AccountID
nitin singh7y ago
Thanks Sreekanth.. your solution is working for me..
Sreekanth Reddy7y ago
Hi Nitin,

From C# try the following once :

var res = duplicateList.Select(i => new {i.AccountId, i.Name}).Distinct().Select(x => new Item {Id = x.AccountId}).ToList();

Mark as answer, If it helps.

Thank you.