asked
Delete minimum salary of duplicate employee details from Employee Table
Hi All, I have given below below simple example then how to delete minimum salary of employee details from employee table.From this example employee 'Sam' having 2 different department and different salary so i need to delete the minimum salary of 'Sam' employee details. CREATE TABLE Testforminimumsalary ( Empname VARCHAR(50), Salary VARCHAR(10), DepatID VARCHAR(10) ) INSERT INTO Testforminimumsalary VALUES('Sam','1000','Engg') INSERT INTO Testforminimumsalary VALUES('Sam','900','HR') ---DuplicateName INSERT INTO Testforminimumsalary VALUES('Smith','2000','Engg') INSERT INTO Testforminimumsalary VALUES('Denis','1500','HR') INSERT INTO Testforminimumsalary VALUES('Danny','3000','HR') INSERT INTO Testforminimumsalary VALUES('David','2000','IT') INSERT INTO Testforminimumsalary VALUES('John','3000','IT') SELECT * FROM Testforminimumsalary
There's a neat trick [using a CTE and the ROW_NUMBER() function to deduplicate data. I blogged it][1] a few years ago. TL;DR: something like this: DECLARE @Testforminimumsalary TABLE ( Empname VARCHAR(50) , Salary VARCHAR(10) , DepartID VARCHAR(10) ) INSERT INTO @Testforminimumsalary VALUES ( 'Sam', '1000', 'Engg' ) INSERT INTO @Testforminimumsalary VALUES ( 'Sam', '900', 'HR' ) INSERT INTO @Testforminimumsalary VALUES ( 'Smith', '2000', 'Engg' ) INSERT INTO @Testforminimumsalary VALUES ( 'Denis', '1500', 'HR' ) INSERT INTO @Testforminimumsalary VALUES ( 'Danny', '3000', 'HR' ) INSERT INTO @Testforminimumsalary VALUES ( 'David', '2000', 'IT' ) INSERT INTO @Testforminimumsalary VALUES ( 'John', '3000', 'IT' ) SELECT * FROM @Testforminimumsalary; WITH TempCTE AS ( SELECT Empname , Salary , DepartID , ROW_NUMBER() OVER ( PARTITION BY Empname ORDER BY Salary DESC ) AS RowNum FROM @Testforminimumsalary ) DELETE FROM TempCTE WHERE TempCTE.RowNum <> 1; SELECT * FROM @Testforminimumsalary [1]:
https://thelonedba.wordpress.com/2011/07/27/deduplicating-data-with-a-cte/
18 People are following this question.
