Write the query to remove the duplicates from a table without using a temporary table?
Question
TheDataMonk
Follow Me
About TheDataMonkGrand Master
I am the Co-Founder of The Data Monk. I have a total of 6+ years of analytics experience 3+ years at Mu Sigma 2 years at OYO 1 year and counting at The Data Monk I am an active trader and a logically sarcastic idiot :)Follow Me
Related questions
The Given Data set, Table has transitive dependencies. Explain Which Attributes are partially dependent. How will you overcome this? Explain the approach?
Write a query to Plot a Relationship between the highest followers in every country (Country Wise Highest Followers) and show which country has the highest followers and which country has the lowest one. Explain the approach?
Write a Query to Find the account with the highest number of followers in every country. Explain the approach?
Assign Rank To each Category by authentic engagement in each country and return the category with a rank less than 3. Explain the Approach?
Write a Query to check for Missing values in the given table? Explain how will you deal with the missing data for qualitative and quantitative data?
Answers ( 4 )
DELETE e1 FROM EMPLOYEE e1, EMPLOYEE e2 WHERE e1.name = e2.name AND e1.id > e2.id;
— Using CTE for deletion instead of temp table , because cte scope is limited to query only
WITH cte_1 AS (
SELECT emp_id, name , ROW_NUMBER() OVER (PARTITION BY emp_id, name ORDER BY emp_id, name) AS row_num
FROM Employee
)
DELETE FROM cte_1
WHERE row_num > 1;
DELETE FROM STUDENT
Where rollno=(Select rollno from Student
Group by rollno
Having count (rollno)>1
Order by count (rollno) desc;)
-using delete join
Delete t1 from table t1
inner join table t2
where t1.name = t2.name
and t1.id > t2.id
— using row_number()
delete from table where id in
(select id from
(select id , name, row_number() over (partition by name order by name) as row_num from table) t
where row_num > 1) ;