Blog author:
Sourabh07 | Posted on: 10/14/2012 | Category:
SQL Server Blogs | Views: 720 | Status:
[Member] |
Points: 75
|
Alert Moderator
To delete the duplicate rows from the sql table... i am using temperately table and with clause to do so. Also for practice point of view, I want you to run the following script in your local database and do your Rn D's to extract more knowledge from it.
Declare @temp table(
id int,
name varchar(50)
)
Insert into @temp (id,name) values (1,'A')
Insert into @temp (id,name) values (2,'B')
Insert into @temp (id,name) values (3,'C')
Insert into @temp (id,name) values (1,'A')
Insert into @temp (id,name) values (2,'B')
Select * from @temp;
With cust as (select ROW_NUMBER() over (partition by id,name order by id) as Row_Cnt,id,name from @temp)
delete from cust where Row_Cnt>1
Select * from @temp order by id
The Output will be:
id name
----------- --------------------------------------------------
1 A
2 B
3 C
1 A
2 B
id name
----------- --------------------------------------------------
1 A
2 B
3 C
The 1st table listing duplicate rows and the 2nd table listing the rows after deleting the duplicate rows.
so, the rows will be displayed in the second table are unique.
It is one of the most frequent question asked in an Interview. So, hopefully it will be helpful for all.
Sourabh07
Found interesting? Add this to: