Great explanation on different approaches of deleting duplicates. How to delete duplicates without creating backup table when there are duplicates with exact same rows. Below would give an error saying cte does not exist. with cte as (select *, row_number() over(partition by emp_id order by emp_id) rwn from employee) delete from cte where rwn > 1

Great video sir💯 Most of the time I have faced duplication issues with entire row So I used row_number to take care of that

Great Video Sir !! Also, for SQL Server, the best approach would be Row_Number(), that will solve both the problems.

Thanks a lot greatly explained...

create table employee ( emp_id int, emp_name varchar(20), salary int, create_timestamp time); INsert into employee values (1, 'Ankit', 20000, GETDATE()), (2, 'Rahul', 20000, GETDATE()), (3, 'Agam', 30000, GETDATE()), (1, 'Ankit', 15000, GETDATE())

Thank you for making such insightful videos on sql. Please advise some do's and don'ts in an interview or how to approach a problem statement.

Great approach!

Nice explanation. May i know which IDE you are using?

Hello sir, Can we use this approach also to remove multiple duplicates with cte as( select *, row_number() over(partition by emp_id order by timestamp desc) as rn from employee) delete from cte where rn>1

Great video sir❤

Nice explnation

Thank You So So Much Ankit for putting in such great efforts....💯💯 But I have one Question How to delete pure duplicates without taking a backup of the table? Is It Possible?

MERGE INTO your_table tgt USING ( SELECT ROWID AS rid, name, ROW_NUMBER() OVER ( PARTITION BY name ORDER BY ROWNUM ) As rn FROM your_table ) src ON (src.rid = tgt.ROWID AND src.rn > 1) WHEN MATCHED THEN UPDATE SET name = name DELETE WHERE 1 = 1; Unfortunatlly this will not work in Sql Server or at last me i couldn t make it work but in Oracle work like a charm. Happy Holydays.

With cte as (Select*, row_number() over(partition by emp_id order by create_timestamp asc) as rank from employee a) Select emp_id, max(create_timestamp) From cte Group by emp_id;

Please provide optimised query for to detect duplicates when we will having million rows and want query to run fast

Hello Ankit Just wanted a little help . How is the course different from all the videos you have put . What additional knowledge will we get ?

Please give tips on not going blank during intrvws. The intrvwer acts hurry hurry and i end up saying 'i dont know' even though later i realise i knew the answer. So many opportunities missed due to this fear.😓😔😔😔

Mind blowing🤯🤯🤯..! Why using the alias A when you havnt used it anywhere like A.empid or A.salary?? This doubt follows me evrywhere..😓😔😔

Ankit, your videos are just fabulous my friend. I am using SQL Server, While deleting duplicate records with multiple columns I am getting an error ("An expression of non-boolean type specified in a context where a condition is expected, near ','). Is it because that ,SQL Server doesn't support multiple column subqueries? Please confirm. Thanks in advance..

thankyou

Please provide create table and insert statement like you used to provide in your odl videos

Hey, what about if i have duplicate record but its id is different. In that case how would we delete that record?

Hello Ankit, is there a way to delete pure duplicate record without taking backup of original table. Please help me with this. Thanks in advance.

Hi..Can you share the name of the application you are using..This is not MS SQL Server

Hi can u provide the database you use so that it will be useful for practicing

Hi Sir, thanks again for wonderful video, I tried deleting in workbench platform but it is not allowing me to delete. I have uncheck the safe mode as well. Error: You can't specify target table 'employee_new' for update in FROM clause. Same query I am using it: delete from employee_new where (emp_id, create_timestamp) = (select emp_id, min(create_timestamp) as create_timestamp from employee_new group by 1 having count(1) >1);

I am getting this error "An expression of non-boolean type specified in a context where a condition is expected, near ','." after running query delete from EMP2 where (Emp_id,create_date) in (select Emp_id, min(create_Date) as time_c from EMP2 group by Emp_ID having count(1)>1) Please comment what needs to be update in this

Please continue the tutorials in SSMS, this editor does not seem to go well

Not the best video in terms of video production but well explained a few things.

Please sir strat SQL courses from basic to Advance.

Accent too heavy , thanks for content tho

somewhat confusing...

Good SQL problems but explanations not that good. Will love to watch if you speak in language you are comfartable to explain