[Stackoverflow good question] Remove duplicate rows in SQL, stackoverflowsql

[Stackoverflow good question] Remove duplicate rows in SQL, stackoverflowsql
ProblemSuppose there is a table with a large data volume (for example, 300,000 + rows) with duplicate rows (except for the primary key, the data in other columns is the same). How can we quickly remove duplicates? My table is similar to this

MyTable-----------RowID int not null identity(1,1) primary key,Col1 varchar(20) not null,Col2 varchar(2048) not null,Col3 tinyint not null

Excellent answerIf there is no null value, you can first perform group by on other columns, then only retain MIN or MAX (RowId), and delete other rows:

DELETE MyTable FROM MyTableLEFT OUTER JOIN (   SELECT MIN(RowId) as RowId, Col1, Col2, Col3    FROM MyTable    GROUP BY Col1, Col2, Col3) as KeepRows ON   MyTable.RowId = KeepRows.RowIdWHERE   KeepRows.RowId IS NULL

If the RowId is not of the int type but a GUID, you can use

CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn))) 

Stackoverflow link: http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows
Column introduction: I like stackoverflow very much and can always find a solution to the problem. The website has a popularity list. So I selected some hot questions and sorted out the discussion based on my own understanding. Therefore, these articles are not really translated, but have made some additions, deletions, and retouching based on your own understanding. I hope that the above discussions can be shared with you more concisely and effectively. If you need to reprint, please specify the original addressHttp://blog.csdn.net/lizeyang