Delete Duplicate rows in table using Common Table Expression

Anu.Malik
Posted by Anu.Malik under Sql Server category on | Points: 40 | Views : 3010
--Table Creation Query

CREATE TABLE EmployeeMas

(Firstname varchar(10),

)


--Insert Query

insert into EmployeeMas(Firstname) Values ('DotNet');
insert into EmployeeMas(Firstname) Values ('DotNet');
insert into EmployeeMas(Firstname) Values ('DotNet');
insert into EmployeeMas(Firstname) Values ('DotNet');
insert into EmployeeMas(Firstname) Values ('DotNet');
insert into EmployeeMas(Firstname) Values ('DotNet');

--Select All Rows
Select * From EmployeeMas

--Delete Query

With Cte(Rownum)
AS(
Select Row_Number() Over (Partition by Firstname Order by Firstname) As Rownum From EmployeeMas)

Delete From Cte Where Rownum>1

--Again Select all rows
Select * From EmployeeMas

Comments or Responses

Posted by: gurramssr-15101 on: 7/6/2012 Level:Starter | Status: [Member] | Points: 10
With Cte(Rownum)
this is giving me an compiler error
Posted by: Anu.Malik on: 7/6/2012 Level:Starter | Status: [Member] | Points: 10
Common Table Expression (CTE) are introduced in SQL Server 2005. So Which version you are using of Sql server.
And Please tell me detail of error which you are facing
Posted by: Sqldev on: 3/11/2013 Level:Starter | Status: [Member] | Points: 10
Hi,

Below video have complete information about CTE like what is CTE , properties of CTE and examples for CTE in sql server.

http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be


Best Regards,
Sql Expert
http://learnsqltips.blogspot.com/
Posted by: Sqldev on: 3/11/2013 Level:Starter | Status: [Member] | Points: 10
Hi,

Below video have complete information about CTE like what is CTE , properties of CTE and examples for CTE in sql server.

http://www.youtube.com/watch?v=Ly-YqPVdVOk&feature=youtu.be


Best Regards,
Sql Expert
http://learnsqltips.blogspot.com/

Login to post response