How to remove duplicate data from a purely duplicated table without adding a new column: SQL Server

Applicable: Developer

Problem

We all know how to find out a duplicated row using 'Group By' and some other techniques, but how do we remove them if it is duplicated with respect to every single column available in the table. So in this session we are going to see 'How To delete row(s) from a SQL Server table keeping one row for each distinct values without using a new table or adding a new column to an existing table'.

Solution

So the purpose of this session we are going to create a table with 3 columns and populate it with just 2 distinct values repeated twice so a total of 4 rows only.

Here we have the data duplicated with every single column. What we are expecting is that we have to eliminate row 2 and row 4 from this table since we already have them but we don't have any number reference to the rows. Here comes the "CTE"! Maybe you already know about CTE for those who don't know about CTEs it's just like a temporary table derived from a query but it's scope limited to only the statement defining it. You can have more information by searching on the online forums. Here we are going to create a CTE with just one column with act as the row number reference to the base table. Let's see..

As you can see we have used ROW_NUMBER() to generate row number for each row . This function starts from 1 and for each new value in at least one of the three columns specified in the "partition" clause it resets the value to 1 or else(duplicate data) continue like 2,3 and so on. Then we deleted every single row from CTE which having a row number value which is greater than 1. Now comes the real magic

Tada! We have eliminated the duplicated data from our base table. I hope you have got benefit on spending time on this session.