<p>I need to remove duplicate rows from a fairly large SQL Server table (i.e. 300,000+ rows).</p>
<p>The rows, of course, will not be perfect duplicates because of the existence of the <code>RowID</code> identity field.</p>
<p><strong>MyTable</strong></p>
<pre><code>RowID int not null identity(1,1) primary key,
Col1 varchar(20) not null,
Col2 varchar(2048) not null,
Col3 tinyint not null
</code></pre>
<p>How can I do this?</p>

[{'answer_id': 18934, 'author': 'Jon Galloway', 'author_id': 5, 'author_profile': 'https://Stackoverflow.com/users/5', 'pm_score': 7, 'selected': False, 'text': '<p>There\'s a good article on <a href="http://support.microsoft.com/kb/139444" rel="noreferrer">removing duplicates</a> on the Microsoft Support site. It\'s pretty conservative - they have you do everything in separate steps - but it should work well against large tables.</p>\n\n<p>I\'ve used self-joins to do this in the past, although it could probably be prettied up with a HAVING clause:</p>\n\n<pre><code>DELETE dupes\nFROM MyTable dupes, MyTable fullTable\nWHERE dupes.dupField = fullTable.dupField \nAND dupes.secondDupField = fullTable.secondDupField \nAND dupes.uniqueField &gt; fullTable.uniqueField\n</code></pre>\n'}, {'answer_id': 18947, 'author': 'Craig', 'author_id': 1611, 'author_profile': 'https://Stackoverflow.com/users/1611', 'pm_score': 4, 'selected': False, 'text': '<p>Here is another good article on <a href="http://www.xaprb.com/blog/2007/02/06/how-to-delete-duplicate-rows-with-sql-part-2/" rel="noreferrer">removing duplicates</a>.</p>\n\n<p>It discusses why its hard: "<em>SQL is based on relational algebra, and duplicates cannot occur in relational algebra, because duplicates are not allowed in a set.</em>"</p>\n\n<p>The temp table solution, and two mysql examples.</p>\n\n<p>In the future are you going to prevent it at a database level, or from an application perspective.  I would suggest the database level because your database should be responsible for maintaining referential integrity, developers just will cause problems ;)</p>\n'}, {'answer_id': 18949, 'author': 'Mark Brackett', 'author_id': 2199, 'author_profile': 'https://Stackoverflow.com/users/2199', 'pm_score': 11, 'selected': True, 'text': "<p>Assuming no nulls, you <code>GROUP BY</code> the unique columns, and <code>SELECT</code> the <code>MIN (or MAX)</code> RowId as the row to keep. Then, just delete everything that didn't have a row id:</p>\n\n<pre><code>DELETE FROM MyTable\nLEFT OUTER JOIN (\n   SELECT MIN(RowId) as RowId, Col1, Col2, Col3 \n   FROM MyTable \n   GROUP BY Col1, Col2, Col3\n) as KeepRows ON\n   MyTable.RowId = KeepRows.RowId\nWHERE\n   KeepRows.RowId IS NULL\n</code></pre>\n\n<p>In case you have a GUID instead of an integer, you can replace</p>\n\n<pre><code>MIN(RowId)\n</code></pre>\n\n<p>with</p>\n\n<pre><code>CONVERT(uniqueidentifier, MIN(CONVERT(char(36), MyGuidColumn)))\n</code></pre>\n"}, {'answer_id': 18983, 'author': 'Jacob Proffitt', 'author_id': 1336, 'author_profile': 'https://Stackoverflow.com/users/1336', 'pm_score': 4, 'selected': False, 'text': '<p>Oh sure. Use a temp table. If you want a single, not-very-performant statement that "works" you can go with:</p>\n\n<pre><code>DELETE FROM MyTable WHERE NOT RowID IN\n    (SELECT \n        (SELECT TOP 1 RowID FROM MyTable mt2 \n        WHERE mt2.Col1 = mt.Col1 \n        AND mt2.Col2 = mt.Col2 \n        AND mt2.Col3 = mt.Col3) \n    FROM MyTable mt)\n</code></pre>\n\n<p>Basically, for each row in the table, the sub-select finds the top RowID of all rows that are exactly like the row under consideration. So you end up with a list of RowIDs that represent the "original" non-duplicated rows.</p>\n'}, {'answer_id': 19034, 'author': 'DrPizza', 'author_id': 2131, 'author_profile': 'https://Stackoverflow.com/users/2131', 'pm_score': 3, 'selected': False, 'text': "<blockquote>\n  <p>From the application level (unfortunately). I agree that the proper way to prevent duplication is at the database level through the use of a unique index, but in SQL Server 2005, an index is allowed to be only 900 bytes, and my varchar(2048) field blows that away.</p>\n</blockquote>\n\n<p>I dunno how well it would perform, but I think you could write a trigger to enforce this, even if you couldn't do it directly with an index.  Something like:</p>\n\n<pre><code>-- given a table stories(story_id int not null primary key, story varchar(max) not null)\nCREATE TRIGGER prevent_plagiarism \nON stories \nafter INSERT, UPDATE \nAS \n    DECLARE @cnt AS INT \n\n    SELECT @cnt = Count(*) \n    FROM   stories \n           INNER JOIN inserted \n                   ON ( stories.story = inserted.story \n                        AND stories.story_id != inserted.story_id ) \n\n    IF @cnt &gt; 0 \n      BEGIN \n          RAISERROR('plagiarism detected',16,1) \n\n          ROLLBACK TRANSACTION \n      END \n</code></pre>\n\n<p>Also, varchar(2048) sounds fishy to me (some things in life are 2048 bytes, but it's pretty uncommon); should it really not be varchar(max)?</p>\n"}, {'answer_id': 839710, 'author': 'Community', 'author_id': -1, 'author_profile': 'https://Stackoverflow.com/users/-1', 'pm_score': 3, 'selected': False, 'text': '<ol>\n<li><p>Create new blank table with the same structure</p></li>\n<li><p>Execute query like this</p>\n\n<pre><code>INSERT INTO tc_category1\nSELECT *\nFROM tc_category\nGROUP BY category_id, application_id\nHAVING count(*) &gt; 1\n</code></pre></li>\n<li><p>Then execute this query</p>\n\n<pre><code>INSERT INTO tc_category1\nSELECT *\nFROM tc_category\nGROUP BY category_id, application_id\nHAVING count(*) = 1\n</code></pre></li>\n</ol>\n'}, {'answer_id': 1888176, 'author': 'codegoalie', 'author_id': 12852, 'author_profile': 'https://Stackoverflow.com/users/12852', 'pm_score': 4, 'selected': False, 'text': "<p>I had a table where I needed to preserve non-duplicate rows.\nI'm not sure on the speed or efficiency.</p>\n\n<pre><code>DELETE FROM myTable WHERE RowID IN (\n  SELECT MIN(RowID) AS IDNo FROM myTable\n  GROUP BY Col1, Col2, Col3\n  HAVING COUNT(*) = 2 )\n</code></pre>\n"}, {'answer_id': 3822833, 'author': 'Martin Smith', 'author_id': 73226, 'author_profile': 'https://Stackoverflow.com/users/73226', 'pm_score': 10, 'selected': False, 'text': '<p>Another possible way of doing this is</p>\n\n<pre><code>; \n\n--Ensure that any immediately preceding statement is terminated with a semicolon above\nWITH cte\n     AS (SELECT ROW_NUMBER() OVER (PARTITION BY Col1, Col2, Col3 \n                                       ORDER BY ( SELECT 0)) RN\n         FROM   #MyTable)\nDELETE FROM cte\nWHERE  RN &gt; 1;\n</code></pre>\n\n<p>I am using <code>ORDER BY (SELECT 0)</code> above as it is arbitrary which row to preserve in the event of a tie. </p>\n\n<p>To preserve the latest one in <code>RowID</code> order for example you could use <code>ORDER BY RowID DESC</code> </p>\n\n<p><strong>Execution Plans</strong></p>\n\n<p>The execution plan for this is often simpler and more efficient than that in the accepted answer as it does not require the self join.</p>\n\n<p><a href="https://i.stack.imgur.com/ZJiWF.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/ZJiWF.jpg" alt="Execution Plans"></a></p>\n\n<p>This is not always the case however. One place where the <code>GROUP BY</code> solution might be preferred is situations where a <a href="https://learn.microsoft.com/en-gb/archive/blogs/craigfr/hash-aggregate" rel="noreferrer">hash aggregate</a> would be chosen in preference to a stream aggregate. </p>\n\n<p>The <code>ROW_NUMBER</code> solution will always give pretty much the same plan whereas the <code>GROUP BY</code> strategy is more flexible.</p>\n\n<p><a href="https://i.stack.imgur.com/iUlWm.jpg" rel="noreferrer"><img src="https://i.stack.imgur.com/iUlWm.jpg" alt="Execution Plans"></a></p>\n\n<p>Factors which might favour the hash aggregate approach would be </p>\n\n<ul>\n<li>No useful index on the partitioning columns</li>\n<li>relatively fewer groups with relatively more duplicates in each group </li>\n</ul>\n\n<p>In extreme versions of this second case (if there are very few groups with many duplicates in each) one could also consider simply inserting the rows to keep into a new table then <code>TRUNCATE</code>-ing the original and copying them back to minimise logging compared to deleting a very high proportion of the rows.</p>\n'}, {'answer_id': 3827302, 'author': 'SoftwareGeek', 'author_id': 168882, 'author_profile': 'https://Stackoverflow.com/users/168882', 'pm_score': 6, 'selected': False, 'text': '<pre><code>delete t1\nfrom table t1, table t2\nwhere t1.columnA = t2.columnA\nand t1.rowid&gt;t2.rowid\n</code></pre>\n\n<p>Postgres: </p>\n\n<pre><code>delete\nfrom table t1\nusing table t2\nwhere t1.columnA = t2.columnA\nand t1.rowid &gt; t2.rowid\n</code></pre>\n'}, {'answer_id': 8244823, 'author': 'gngolakia', 'author_id': 1050111, 'author_profile': 'https://Stackoverflow.com/users/1050111', 'pm_score': 7, 'selected': False, 'text': '<p>The following query is useful to delete duplicate rows. The table in this example has <code>ID</code> as an identity column and the columns which have duplicate data are <code>Column1</code>, <code>Column2</code> and <code>Column3</code>.</p>\n\n<pre><code>DELETE FROM TableName\nWHERE  ID NOT IN (SELECT MAX(ID)\n                  FROM   TableName\n                  GROUP  BY Column1,\n                            Column2,\n                            Column3\n                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially\n                    nullable. Because of semantics of NOT IN (NULL) including the clause\n                    below can simplify the plan*/\n                  HAVING MAX(ID) IS NOT NULL) \n</code></pre>\n\n<p>The following script shows usage of <code>GROUP BY</code>, <code>HAVING</code>, <code>ORDER BY</code> in one query, and returns the results with duplicate column and its count.</p>\n\n<pre><code>SELECT YourColumnName,\n       COUNT(*) TotalCount\nFROM   YourTableName\nGROUP  BY YourColumnName\nHAVING COUNT(*) &gt; 1\nORDER  BY COUNT(*) DESC \n</code></pre>\n'}, {'answer_id': 9193001, 'author': 'Sudhakar NV', 'author_id': 1197119, 'author_profile': 'https://Stackoverflow.com/users/1197119', 'pm_score': 3, 'selected': False, 'text': '<p>By useing below query we can able to delete duplicate records based on the single column or multiple column. below query is deleting based on two columns. table name is: <code>testing</code> and column names <code>empno,empname</code></p>\n\n<pre><code>DELETE FROM testing WHERE empno not IN (SELECT empno FROM (SELECT empno, ROW_NUMBER() OVER (PARTITION BY empno ORDER BY empno) \nAS [ItemNumber] FROM testing) a WHERE ItemNumber &gt; 1)\nor empname not in\n(select empname from (select empname,row_number() over(PARTITION BY empno ORDER BY empno) \nAS [ItemNumber] FROM testing) a WHERE ItemNumber &gt; 1)\n</code></pre>\n'}, {'answer_id': 11431968, 'author': 'AnandPhadke', 'author_id': 1495994, 'author_profile': 'https://Stackoverflow.com/users/1495994', 'pm_score': 3, 'selected': False, 'text': '<pre><code>CREATE TABLE car(Id int identity(1,1), PersonId int, CarId int)\n\nINSERT INTO car(PersonId,CarId)\nVALUES(1,2),(1,3),(1,2),(2,4)\n\n--SELECT * FROM car\n\n;WITH CTE as(\nSELECT ROW_NUMBER() over (PARTITION BY personid,carid order by personid,carid) as rn,Id,PersonID,CarId from car)\n\nDELETE FROM car where Id in(SELECT Id FROM CTE WHERE rn&gt;1)\n</code></pre>\n'}, {'answer_id': 12818055, 'author': 'heta77', 'author_id': 1734652, 'author_profile': 'https://Stackoverflow.com/users/1734652', 'pm_score': 4, 'selected': False, 'text': '<pre><code>SELECT  DISTINCT *\n      INTO tempdb.dbo.tmpTable\nFROM myTable\n\nTRUNCATE TABLE myTable\nINSERT INTO myTable SELECT * FROM tempdb.dbo.tmpTable\nDROP TABLE tempdb.dbo.tmpTable\n</code></pre>\n'}, {'answer_id': 14612370, 'author': 'Evgueny Sedov', 'author_id': 1193024, 'author_profile': 'https://Stackoverflow.com/users/1193024', 'pm_score': 3, 'selected': False, 'text': '<p>I would mention this approach as well as it can be helpful, and works in all SQL servers:\nPretty often there is only one - two duplicates, and  Ids and count of duplicates are known. In this case:</p>\n\n<pre><code>SET ROWCOUNT 1 -- or set to number of rows to be deleted\ndelete from myTable where RowId = DuplicatedID\nSET ROWCOUNT 0\n</code></pre>\n'}, {'answer_id': 14717523, 'author': 'JuanJo', 'author_id': 2044799, 'author_profile': 'https://Stackoverflow.com/users/2044799', 'pm_score': 5, 'selected': False, 'text': '<p>Quick and Dirty to delete exact duplicated rows (for small tables):</p>\n\n<pre><code>select  distinct * into t2 from t1;\ndelete from t1;\ninsert into t1 select *  from t2;\ndrop table t2;\n</code></pre>\n'}, {'answer_id': 18086447, 'author': 'Nitish Pareek', 'author_id': 918385, 'author_profile': 'https://Stackoverflow.com/users/918385', 'pm_score': 4, 'selected': False, 'text': '<p>Yet another easy solution can be found at the link pasted <a href="http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server" rel="noreferrer">here</a>. This one easy to grasp and seems to be effective for most of the similar problems. It is for SQL Server though but the concept used is more than acceptable.</p>\n\n<p>Here are the relevant portions from the linked page:</p>\n\n<p>Consider this data:</p>\n\n<pre><code>EMPLOYEE_ID ATTENDANCE_DATE\nA001    2011-01-01\nA001    2011-01-01\nA002    2011-01-01\nA002    2011-01-01\nA002    2011-01-01\nA003    2011-01-01\n</code></pre>\n\n<p>So how can we delete those duplicate data?</p>\n\n<p>First, insert an identity column in that table by using the following code:</p>\n\n<pre><code>ALTER TABLE dbo.ATTENDANCE ADD AUTOID INT IDENTITY(1,1)  \n</code></pre>\n\n<p>Use the following code to resolve it:</p>\n\n<pre><code>DELETE FROM dbo.ATTENDANCE WHERE AUTOID NOT IN (SELECT MIN(AUTOID) _\n    FROM dbo.ATTENDANCE GROUP BY EMPLOYEE_ID,ATTENDANCE_DATE) \n</code></pre>\n'}, {'answer_id': 18719814, 'author': 'Syed Mohamed', 'author_id': 2089963, 'author_profile': 'https://Stackoverflow.com/users/2089963', 'pm_score': 5, 'selected': False, 'text': '<p>This will delete duplicate rows, except the first row</p>\n\n<pre><code>DELETE\nFROM\n    Mytable\nWHERE\n    RowID NOT IN (\n        SELECT\n            MIN(RowID)\n        FROM\n            Mytable\n        GROUP BY\n            Col1,\n            Col2,\n            Col3\n    )\n</code></pre>\n\n<p>Refer (<a href="http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server" rel="noreferrer">http://www.codeproject.com/Articles/157977/Remove-Duplicate-Rows-from-a-Table-in-SQL-Server</a>)</p>\n'}, {'answer_id': 18865386, 'author': 'Ismail Yavuz', 'author_id': 2290369, 'author_profile': 'https://Stackoverflow.com/users/2290369', 'pm_score': 3, 'selected': False, 'text': "<p>The other way is <strong>Create a new</strong> table with same fields and <strong>with Unique Index</strong>. Then <strong>move all data from old table to new table</strong>. Automatically SQL SERVER ignore (there is also an option about what to do if there will be a duplicate value: ignore, interrupt or sth) duplicate values. So we have the same table without duplicate rows. <strong>If you don't want Unique Index, after the transfer data you can drop it</strong>.</p>\n\n<p>Especially <strong>for larger tables</strong> you may use DTS (SSIS package to import/export data) in order to transfer all data rapidly to your new uniquely indexed table. For 7 million row it takes just a few minute.</p>\n"}, {'answer_id': 19152091, 'author': 'Teena', 'author_id': 2841400, 'author_profile': 'https://Stackoverflow.com/users/2841400', 'pm_score': 3, 'selected': False, 'text': '<pre><code>DELETE\nFROM\n    table_name T1\nWHERE\n    rowid &gt; (\n        SELECT\n            min(rowid)\n        FROM\n            table_name T2\n        WHERE\n            T1.column_name = T2.column_name\n    );\n</code></pre>\n'}, {'answer_id': 20886125, 'author': 'Jayron Soares', 'author_id': 2665070, 'author_profile': 'https://Stackoverflow.com/users/2665070', 'pm_score': 3, 'selected': False, 'text': '<pre><code>DELETE \nFROM MyTable\nWHERE NOT EXISTS (\n              SELECT min(RowID)\n              FROM Mytable\n              WHERE (SELECT RowID \n                     FROM Mytable\n                     GROUP BY Col1, Col2, Col3\n                     ))\n               );\n</code></pre>\n'}, {'answer_id': 21380738, 'author': 'Ruben Verschueren', 'author_id': 1396478, 'author_profile': 'https://Stackoverflow.com/users/1396478', 'pm_score': 4, 'selected': False, 'text': "<p>I thought I'd share my solution since it works under special circumstances.\nI my case the table with duplicate values did not have a foreign key (because the values were duplicated from another db).</p>\n\n<pre><code>begin transaction\n-- create temp table with identical structure as source table\nSelect * Into #temp From tableName Where 1 = 2\n\n-- insert distinct values into temp\ninsert into #temp \nselect distinct * \nfrom  tableName\n\n-- delete from source\ndelete from tableName \n\n-- insert into source from temp\ninsert into tableName \nselect * \nfrom #temp\n\nrollback transaction\n-- if this works, change rollback to commit and execute again to keep you changes!!\n</code></pre>\n\n<p>PS: when working on things like this I always use a transaction, this not only ensures everything is executed as a whole, but also allows me to test without risking anything. But off course you should take a backup anyway just to be sure...</p>\n"}, {'answer_id': 22111625, 'author': 'James Errico', 'author_id': 832005, 'author_profile': 'https://Stackoverflow.com/users/832005', 'pm_score': 4, 'selected': False, 'text': '<p>I prefer the subquery\\having count(*) > 1 solution to the inner join because I found it easier to read and it was very easy to turn into a SELECT statement to verify what would be deleted before you run it. </p>\n\n<pre><code>--DELETE FROM table1 \n--WHERE id IN ( \n     SELECT MIN(id) FROM table1 \n     GROUP BY col1, col2, col3 \n     -- could add a WHERE clause here to further filter\n     HAVING count(*) &gt; 1\n--)\n</code></pre>\n'}, {'answer_id': 23777260, 'author': 'Jithin Shaji', 'author_id': 3265371, 'author_profile': 'https://Stackoverflow.com/users/3265371', 'pm_score': 6, 'selected': False, 'text': '<pre><code>DELETE LU \nFROM   (SELECT *, \n               Row_number() \n                 OVER ( \n                   partition BY col1, col1, col3 \n                   ORDER BY rowid DESC) [Row] \n        FROM   mytable) LU \nWHERE  [row] &gt; 1 \n</code></pre>\n'}, {'answer_id': 26913528, 'author': 'Ostati', 'author_id': 2654100, 'author_profile': 'https://Stackoverflow.com/users/2654100', 'pm_score': 4, 'selected': False, 'text': '<p>Using CTE. The idea is to join on one or more columns that form a duplicate record and then remove whichever you like:</p>\n\n<pre><code>;with cte as (\n    select \n        min(PrimaryKey) as PrimaryKey\n        UniqueColumn1,\n        UniqueColumn2\n    from dbo.DuplicatesTable \n    group by\n        UniqueColumn1, UniqueColumn1\n    having count(*) &gt; 1\n)\ndelete d\nfrom dbo.DuplicatesTable d \ninner join cte on \n    d.PrimaryKey &gt; cte.PrimaryKey and\n    d.UniqueColumn1 = cte.UniqueColumn1 and \n    d.UniqueColumn2 = cte.UniqueColumn2;\n</code></pre>\n'}, {'answer_id': 27409405, 'author': 'Draško', 'author_id': 1176497, 'author_profile': 'https://Stackoverflow.com/users/1176497', 'pm_score': 4, 'selected': False, 'text': '<p>This query showed very good performance for me:</p>\n\n<pre><code>DELETE tbl\nFROM\n    MyTable tbl\nWHERE\n    EXISTS (\n        SELECT\n            *\n        FROM\n            MyTable tbl2\n        WHERE\n            tbl2.SameValue = tbl.SameValue\n        AND tbl.IdUniqueValue &lt; tbl2.IdUniqueValue\n    )\n</code></pre>\n\n<p>it deleted 1M rows in little more than 30sec from a table of 2M (50% duplicates)</p>\n'}, {'answer_id': 27732094, 'author': 'Lauri Lubi', 'author_id': 412368, 'author_profile': 'https://Stackoverflow.com/users/412368', 'pm_score': 3, 'selected': False, 'text': '<p>I you want to preview the rows you are about to remove and keep control over which of the duplicate rows to keep. See <a href="http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/">http://developer.azurewebsites.net/2014/09/better-sql-group-by-find-duplicate-data/</a></p>\n\n<pre><code>with MYCTE as (\n  SELECT ROW_NUMBER() OVER (\n    PARTITION BY DuplicateKey1\n                ,DuplicateKey2 -- optional\n    ORDER BY CreatedAt -- the first row among duplicates will be kept, other rows will be removed\n  ) RN\n  FROM MyTable\n)\nDELETE FROM MYCTE\nWHERE RN &gt; 1\n</code></pre>\n'}, {'answer_id': 30328691, 'author': 'Shamseer K', 'author_id': 4133590, 'author_profile': 'https://Stackoverflow.com/users/4133590', 'pm_score': 5, 'selected': False, 'text': '<p>I would prefer CTE for deleting duplicate rows from sql server table</p>\n\n<p>strongly recommend to follow this article ::<a href="http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/" rel="noreferrer">http://codaffection.com/sql-server-article/delete-duplicate-rows-in-sql-server/</a></p>\n\n<blockquote>\n  <p>by keeping original</p>\n</blockquote>\n\n<pre><code>WITH CTE AS\n(\nSELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN\nFROM MyTable\n)\n\nDELETE FROM CTE WHERE RN&lt;&gt;1\n</code></pre>\n\n<blockquote>\n  <p>without keeping original</p>\n</blockquote>\n\n<pre><code>WITH CTE AS\n(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)\nFROM MyTable)\n\xa0\nDELETE CTE\nWHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)&gt;1)\n</code></pre>\n'}, {'answer_id': 31586339, 'author': 'Haris N I', 'author_id': 5073609, 'author_profile': 'https://Stackoverflow.com/users/5073609', 'pm_score': 4, 'selected': False, 'text': '<p>Use this</p>\n\n<pre><code>WITH tblTemp as\n(\nSELECT ROW_NUMBER() Over(PARTITION BY Name,Department ORDER BY Name)\n   As RowNumber,* FROM &lt;table_name&gt;\n)\nDELETE FROM tblTemp where RowNumber &gt;1\n</code></pre>\n'}, {'answer_id': 34305065, 'author': 'Chanukya', 'author_id': 5093602, 'author_profile': 'https://Stackoverflow.com/users/5093602', 'pm_score': 1, 'selected': False, 'text': '<pre><code>alter table MyTable add sno int identity(1,1)\n    delete from MyTable where sno in\n    (\n    select sno from (\n    select *,\n    RANK() OVER ( PARTITION BY RowID,Col3 ORDER BY sno DESC )rank\n    From MyTable\n    )T\n    where rank&gt;1\n    )\n\n    alter table MyTable \n    drop  column sno\n</code></pre>\n'}, {'answer_id': 34730529, 'author': 'Hamit YILDIRIM', 'author_id': 914284, 'author_profile': 'https://Stackoverflow.com/users/914284', 'pm_score': 0, 'selected': False, 'text': '<p>Now lets look elasticalsearch table which this tables has duplicated rows and Id is identical uniq field. We know if some id exist by a group criteria then we can delete other rows outscope of this group. My manner shows this criteria.</p>\n\n<p>So many case of this thread are in the like state of mine. Just change your target group criteria according your case for deleting repeated (duplicated) rows.</p>\n\n<pre><code>DELETE \nFROM elasticalsearch\nWHERE Id NOT IN \n               (SELECT min(Id)\n                     FROM elasticalsearch\n                     GROUP BY FirmId,FilterSearchString\n                     ) \n</code></pre>\n\n<p>cheers</p>\n'}, {'answer_id': 35147002, 'author': 'yuvi', 'author_id': 4919084, 'author_profile': 'https://Stackoverflow.com/users/4919084', 'pm_score': 3, 'selected': False, 'text': '<p>Another way of doing this :--</p>\n\n<pre><code>DELETE A\nFROM   TABLE A,\n       TABLE B\nWHERE  A.COL1 = B.COL1\n       AND A.COL2 = B.COL2\n       AND A.UNIQUEFIELD &gt; B.UNIQUEFIELD \n</code></pre>\n'}, {'answer_id': 37669155, 'author': 'Brett Ryan', 'author_id': 140037, 'author_profile': 'https://Stackoverflow.com/users/140037', 'pm_score': 1, 'selected': False, 'text': "<p>Sometimes a soft delete mechanism is used where a date is recorded to indicate the deleted date. In this case an <code>UPDATE</code> statement may be used to update this field based on duplicate entries.</p>\n\n<pre><code>UPDATE MY_TABLE\n   SET DELETED = getDate()\n WHERE TABLE_ID IN (\n    SELECT x.TABLE_ID\n      FROM MY_TABLE x\n      JOIN (SELECT min(TABLE_ID) id, COL_1, COL_2, COL_3\n              FROM MY_TABLE d\n             GROUP BY d.COL_1, d.COL_2, d.COL_3\n            HAVING count(*) &gt; 1) AS d ON d.COL_1 = x.COL_1\n                                     AND d.COL_2 = x.COL_2\n                                     AND d.COL_3 = x.COL_3\n                                     AND d.TABLE_ID &lt;&gt; x.TABLE_ID\n             /*WHERE x.COL_4 &lt;&gt; 'D' -- Additional filter*/)\n</code></pre>\n\n<p>This method has served me well for fairly moderate tables containing ~30 million rows with high and low amounts of duplications.</p>\n"}, {'answer_id': 39738697, 'author': 'Harikesh Yadav', 'author_id': 6546950, 'author_profile': 'https://Stackoverflow.com/users/6546950', 'pm_score': 4, 'selected': False, 'text': '<p>This is the easiest way to delete duplicate record</p>\n<pre><code> DELETE FROM tblemp WHERE id IN \n (\n  SELECT MIN(id) FROM tblemp\n   GROUP BY  title HAVING COUNT(id)&gt;1\n )\n</code></pre>\n'}, {'answer_id': 41377822, 'author': 'Shaini Sinha', 'author_id': 5887766, 'author_profile': 'https://Stackoverflow.com/users/5887766', 'pm_score': 5, 'selected': False, 'text': '<p><strong>To Fetch Duplicate Rows:</strong><br></p>\n\n<pre><code>SELECT\nname, email, COUNT(*)\nFROM \nusers\nGROUP BY\nname, email\nHAVING COUNT(*) &gt; 1\n</code></pre>\n\n<p><strong>To Delete the Duplicate Rows:</strong></p>\n\n<pre><code>DELETE users \nWHERE rowid NOT IN \n(SELECT MIN(rowid)\nFROM users\nGROUP BY name, email);      \n</code></pre>\n'}, {'answer_id': 43387570, 'author': 'Jakub Ojmucianski', 'author_id': 6696265, 'author_profile': 'https://Stackoverflow.com/users/6696265', 'pm_score': 1, 'selected': False, 'text': "<p>I know that this question has been already answered, but I've created pretty useful sp which will create a dynamic delete statement for a table duplicates:</p>\n\n<pre><code>    CREATE PROCEDURE sp_DeleteDuplicate @tableName varchar(100), @DebugMode int =1\nAS \nBEGIN\nSET NOCOUNT ON;\n\nIF(OBJECT_ID('tempdb..#tableMatrix') is not null) DROP TABLE #tableMatrix;\n\nSELECT ROW_NUMBER() OVER(ORDER BY name) as rn,name into #tableMatrix FROM sys.columns where [object_id] = object_id(@tableName) ORDER BY name\n\nDECLARE @MaxRow int = (SELECT MAX(rn) from #tableMatrix)\nIF(@MaxRow is null)\n    RAISERROR  ('I wasn''t able to find any columns for this table!',16,1)\nELSE \n    BEGIN\nDECLARE @i int =1 \nDECLARE @Columns Varchar(max) ='';\n\nWHILE (@i &lt;= @MaxRow)\nBEGIN \n    SET @Columns=@Columns+(SELECT '['+name+'],' from #tableMatrix where rn = @i)\n\n    SET @i = @i+1;\nEND\n\n---DELETE LAST comma\nSET @Columns = LEFT(@Columns,LEN(@Columns)-1)\n\nDECLARE @Sql nvarchar(max) = '\nWITH cteRowsToDelte\n     AS (\nSELECT ROW_NUMBER() OVER (PARTITION BY '+@Columns+' ORDER BY ( SELECT 0)) as rowNumber,* FROM '+@tableName\n+')\n\nDELETE FROM cteRowsToDelte\nWHERE  rowNumber &gt; 1;\n'\nSET NOCOUNT OFF;\n    IF(@DebugMode = 1)\n       SELECT @Sql\n    ELSE\n       EXEC sp_executesql @Sql\n    END\nEND\n</code></pre>\n\n<p>So if you create table like that:</p>\n\n<pre><code>IF(OBJECT_ID('MyLitleTable') is not null)\n    DROP TABLE MyLitleTable \n\n\nCREATE TABLE MyLitleTable\n(\n    A Varchar(10),\n    B money,\n    C int\n)\n---------------------------------------------------------\n\n    INSERT INTO MyLitleTable VALUES\n    ('ABC',100,1),\n    ('ABC',100,1), -- only this row should be deleted\n    ('ABC',101,1),\n    ('ABC',100,2),\n    ('ABCD',100,1)\n\n    -----------------------------------------------------------\n\n     exec sp_DeleteDuplicate 'MyLitleTable',0\n</code></pre>\n\n<p>It will delete all duplicates from your table. If you run it without the second parameter it will return a SQL statement to run.</p>\n\n<p>If you need to exclude any of the column just run it in the debug mode get the code and modify it whatever you like.</p>\n"}, {'answer_id': 50782285, 'author': 'Selim Reza', 'author_id': 4079929, 'author_profile': 'https://Stackoverflow.com/users/4079929', 'pm_score': 0, 'selected': False, 'text': '<p>I think this would be helpfull. Here, <strong>ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as num</strong> has been used to differentiate  the duplicate rows.</p>\n\n<pre><code>delete FROM\n(SELECT res1.*,ROW_NUMBER() OVER(PARTITION BY res1.Title ORDER BY res1.Id)as num\n FROM \n(select * from [dbo].[tbl_countries])as res1\n)as res2\nWHERE res2.num &gt; 1\n</code></pre>\n'}, {'answer_id': 53047498, 'author': 'Suraj Kumar', 'author_id': 10532500, 'author_profile': 'https://Stackoverflow.com/users/10532500', 'pm_score': 1, 'selected': False, 'text': '<p>If all the columns in duplicate rows are same then below query can be used to delete the duplicate records.</p>\n\n<pre><code>SELECT DISTINCT * INTO #TemNewTable FROM #OriginalTable\nTRUNCATE TABLE #OriginalTable\nINSERT INTO #OriginalTable SELECT * FROM #TemNewTable\nDROP TABLE #TemNewTable\n</code></pre>\n'}, {'answer_id': 63276678, 'author': 'Ankit Jindal', 'author_id': 4198180, 'author_profile': 'https://Stackoverflow.com/users/4198180', 'pm_score': 1, 'selected': False, 'text': '<p>For the table structure</p>\n<p><strong>MyTable</strong></p>\n<pre><code>RowID int not null identity(1,1) primary key,\nCol1 varchar(20) not null,\nCol2 varchar(2048) not null,\nCol3 tinyint not null\n</code></pre>\n<p>The query for removing duplicates:</p>\n<pre><code>DELETE t1\nFROM MyTable t1\nINNER JOIN MyTable t2\nWHERE t1.RowID &gt; t2.RowID\n  AND t1.Col1 = t2.Col1\n  AND t1.Col2=t2.Col2\n  AND t1.Col3=t2.Col3;\n</code></pre>\n<blockquote>\n<p>I am assuming that <code>RowID</code> is kind of auto-increment and rest of the columns have duplicate values.</p>\n</blockquote>\n'}, {'answer_id': 66499181, 'author': 'Mansour Alnasser', 'author_id': 1448379, 'author_profile': 'https://Stackoverflow.com/users/1448379', 'pm_score': 0, 'selected': False, 'text': '<h2>Other way to remove duplicates based on two columns</h2>\n<p>I found this query easier to read and replace.</p>\n<pre class="lang-sql prettyprint-override"><code>DELETE \nFROM \n TABLE_NAME \n WHERE FIRST_COLUMNS \n IN( \n       SELECT * FROM \n           ( SELECT MIN(FIRST_COLUMNS) \n             FROM TABLE_NAME \n             GROUP BY \n                      FIRST_COLUMNS,\n                      SECOND_COLUMNS \n             HAVING COUNT(FIRST_COLUMNS) &gt; 1 \n            ) temp \n   )\n</code></pre>\n<p>note: It\'s good to <code>simulate query</code> before you run it.</p>\n<p><a href="https://i.stack.imgur.com/dwLM3.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/dwLM3.png" alt="enter image description here" /></a></p>\n'}, {'answer_id': 67339432, 'author': 'Chandan Kumar Singh', 'author_id': 8124278, 'author_profile': 'https://Stackoverflow.com/users/8124278', 'pm_score': -1, 'selected': False, 'text': '<p><strong>A very simple way to delete duplicate rows of table in postgresql.</strong></p>\n<pre><code>DELETE FROM table1 a\nUSING table1 b\nWHERE a.id &lt; b.id\nAND a.column1 = b.column1\nAND a.column2 = b.column2;\n</code></pre>\n'}, {'answer_id': 69237652, 'author': 'Md. Tarikul Islam Soikot', 'author_id': 15078671, 'author_profile': 'https://Stackoverflow.com/users/15078671', 'pm_score': 0, 'selected': False, 'text': '<p>First you can select minimum RowId\'s using MIN() and Group By. We will keep these Rows.</p>\n<pre><code>   SELECT MIN(RowId) as RowId\n   FROM MyTable \n   GROUP BY Col1, Col2, Col3\n</code></pre>\n<p>And Delete RowId\'s those are not in selected minimum RowId\'s using</p>\n<pre><code>DELETE FROM MyTable WHERE RowId Not IN()\n</code></pre>\n<p>Final query:</p>\n<pre><code>DELETE FROM MyTable WHERE RowId Not IN(\n\n    SELECT MIN(RowId) as RowId\n    FROM MyTable \n    GROUP BY Col1, Col2, Col3\n)\n</code></pre>\n<p>You can also check my answer in <a href="http://sqlfiddle.com/#!18/6a5dc/5" rel="nofollow noreferrer">SQL Fiddle</a></p>\n'}, {'answer_id': 72659315, 'author': 'Vikas kumar', 'author_id': 5826097, 'author_profile': 'https://Stackoverflow.com/users/5826097', 'pm_score': -1, 'selected': False, 'text': '<p>Delete Duplicate record</p>\n<blockquote>\n<p>Greater Than operator in this case delete all record except first record</p>\n</blockquote>\n<p>DELETE u1 FROM users u1  JOIN users u2\nWHERE u1.id &gt; u2.id\nAND u1.email=u2.email</p>\n<p>&lt; Less than operator in this case delete all record except last record</p>\n<p>DELETE u1 FROM users u1  JOIN users u2\nWHERE u1.id &lt; u2.id\nAND u1.email=u2.email</p>\n'}, {'answer_id': 73993006, 'author': 'michael satumba', 'author_id': 16040742, 'author_profile': 'https://Stackoverflow.com/users/16040742', 'pm_score': -1, 'selected': False, 'text': '<p>Create another table that will consist of original values:</p>\n<pre><code>CREATE TABLE table2 AS SELECT *, COUNT(*) FROM table1 GROUP BY name HAVING COUNT (*) &gt; 0\n</code></pre>\n'}]
['https://Stackoverflow.com/questions/18932', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/357/']