Remove Duplicate Rows In SQL Server Using CTE

In this article, you will learn how to remove duplicate rows in SQL Server using CTE. Database contains duplicate rows which can be filtered out using certain columns. This can happen for many reasons. When we want to show this data in our application, we need to manage these kinds of duplicated rows. So here, I am showing a simple way to remove duplicate rows.

What is CTE?

CTE stands for Common Table Expressions.

We define CTEs by adding a "WITH" clause directly before our SELECT, INSERT, UPDATE, DELETE, or MERGE statement. The WITH clause can include one or more CTEs, as shown in the following syntax.

[WITH <common_table_expression> [,...]]

<common_table_expression>::=
cte_name [(column_name [,...])]
AS (cte_query)

This can be represented like this…

So now, we have come to our main point, i.e., how to remove duplicated rows. Suppose, we have a table called CV_tbl which holds Chk_ID and Cv_ID. The table may contain records like this.

Chk_Id	Cv_ID
1	11
2	12
3	13
1	11
1	11
3	13

Now, we want to select only one distinct pair. Let's see how we can do that.

Take a look at the SQL code below.

With DataCte  as
(select *, RANK( )
over(partition By Chk_Id, Cv_Id order by Chk_Id) as rnk from CV_tbl )
select * from DataCte

In the above SQL, DataCte is the CTE expression which acts as a temporary View. In the query definition, we are using RANK function and partitioning the table rows with Chk_ID and Cv_ID to assign an occurrence number to each pair. The query will return a result as below.

Chk_Id	Cv_ID	rnk
1	11	1
1	11	2
1	11	3
2	12	1
3	13	1
3	13	2

Now, instead of the final SELECT query, we can delete the rows from our temporary result set which has rnk > 1.

With DataCte  as
(select *, RANK( )
over(partition By Chk_Id, Cv_Id order by Chk_Id) as rnk from CV_tbl )
select * from DataCte where rnk =1

Result of the above query.

Chk_ID	Cv_ID	rnk
1	11	1
2	12	1
3	13	1

This will just select only one occurrence of each Chk_ID and Cv_ID pair from the CV_tbl table.