Adobe Interview Question
Software Engineer in TestsCountry: United States
delete from Emp where Emp.EmpId IN (select EmpId from Emp Emp2 where Emp.FirstName = Emp2.FirstName and Emp.LastName = Emp2.LastName and Emp2.EmpId <> Emp.EmpId)
Won't this delete every instance of duplicated data?
Perhaps we should do Emp2.EmpId > Emp.EmpId, rather than <>.
You could use Rank() function to identify the duplicate row
Select Rank() over(Partition by firstname, lastname order by rowid) rank_n, RowId, FirstName, LastName from Employee
The above function will give you the ranking based on the firstname and lastname combination.
delete the records for the identified rowId's from above query where rank_n >1
I am assuming you aren't allowed to use nested queries either. Hence.
DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
`emp_id` int(11) NOT NULL auto_increment,
`first_name` varchar(64) NOT NULL,
`last_name` varchar(64) NOT NULL,
PRIMARY KEY (`emp_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
insert into person (first_name, last_name) values ('bob', 'lync');
insert into person (first_name, last_name) values ('sarah', 'john');
insert into person (first_name, last_name) values ('bob', 'lync');
insert into person (first_name, last_name) values ('john', 'doe');
insert into person (first_name, last_name) values ('stanly', 'jeff');
insert into person (first_name, last_name) values ('sarah', 'john');
select a.* from person a, person b
where a.first_name = b.first_name
and a.last_name = b.last_name
and a.emp_id > b.emp_id;
delete a.*
from person a, person b
where a.first_name = b.first_name
and a.last_name = b.last_name
and a.emp_id > b.emp_id;
Select emp id, first name, last name, count (*) from EMP
Group by first name, last name
where count(*) > 1
this will return the duplicate records and Delete it using delete query....
This code works fine for me. have used Common Table expressions .
WITH CTE_1
AS (
SELECT first_name, last_name, ROW_NUMBER() OVER (ORDER BY first_name,last_name) AS keep_ID ,
DENSE_RANK() OVER(ORDER BY first_name,last_name) AS RANK FROM persons_cup)
DELETE FROM CTE_1 WHERE keep_ID NOT IN
(SELECT MIN(keep_id) FROM CTE_1 B GROUP BY RANK)
delete from employee where rowid not in (select min(rowid) from employee group by firstName , lastName)
- entityvsentityv2 August 02, 2012