Answer: create table:
Create table emp (Eno int ,EmpName varchar(10),Dept varchar(10))
Insert Values:
insert into emp values(1,'John','IT')
insert into emp values(1,'John','IT')
insert into emp values(2,'Maxx','CSE')
insert into emp values(3,'Cany','AUTO')
insert into emp values(2,'MAXX','CSE')
insert into emp values(1,'SONY','IT')
Select * from emp
Eno EmpName Dept
1 John IT
2 Maxx CSE
3 Cany AUTO
2 MAXX CSE
1 SONY IT
1 John IT
Find the duplicate values from the table:
WITH tempTable as(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)
select * from tempTable where RowNumber >1
Delete duplicate Rows with CTE
WITH tempTable as(
SELECT ROW_NUMBER() Over(PARTITION BY eno,empname ORDER BY eno) As RowNumber,* FROM emp
)
DELETE FROM tempTable where RowNumber >1
---After Deleting the duplicate values
select * from emp
Eno EmpName Dept
1 John IT
3 Cany AUTO
2 MAXX CSE
1 SONY IT
Asked In: Many Interviews |
Alert Moderator