✨Link to my SQL e-book: imojo.in/rkajq9 ✨Link to 'Switch your career easily' e-book: imojo.in/1FL7KaG (Perfect for anyone willing to switch to a completely new field or to a different company in the Tech World)
It's a great post Sadia, no matter how many years of experience we have ,it's always a good revision for any one # Additional Reference There are also other ways to restrict the duplicates 1. UNIQUE 2. UNION 3. ROW_NUMBER | RANK () | DENSE_RANK () - Analytical Function 4. INTERSECT 5. MINUS 6. ROWID | ROWID + CORRELATED SUBQUERY 7. GROUP BY
Mam it would be great if you can take up topics or concepts which are really important like this though not much frequently asked in interviews and also it is useful for an engineer to know all these as well
Hii.. Please clear one question in this video ..In CTE inner query ..you have not used * after select keyword ..you have directly written " select row_number()..." .... * aaega na after select ??
Hi just one suggestion you first way will always not would the solution for routine. N in your second way we have to design table with identify type then you will be able to access
very good effort and I appreciate your clips. However I have a question; suppose application messed up and gave same ID to two (2) different unique valid name. eg. 5001 to ABC and 5001 to XYZ and we want to keep both that is they should exists in our resultant with 2 unique IDs. Please don't get me wrong I am not trying to trick you, I had this real situation once with thousands of rows and was stuck.
In that scenario, you need to add ID, name in partition. DELETE FROM ( SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY ID) AS Rno FROM emp ) AS E WHERE Rno > 1; This query will delete duplicate rows from the "emp" table where duplicates are determined by the combination of "ID" and "NAME" columns, keeping only the first occurrence and removing the rest.
What happen if there are more than 50 columns with the duplicate records Instead of passing all the columns names to the partition by clause Is there any way to put * . Any thoughts?
it will be really helpful , if you do this on your system, please, it should be a live workout from your end, instead of explaining it on the written paper.
Hello mam, deleting using CTE throws the following error.... "Error Code: 1288. The target table cte of the DELETE is not updatable" how to resolve it?
Hi..one question i have ..pls rply ASAP... I'm using MYSQL and MYSQL doesn't support select * into.. therefore i used insert into newtable select distinct * from old table ...here i faced one issue...i hv one tble with eid,ename and salary. eid field is PK and ename and salary has duplicate values ...now when i use insert into using distinct keyword it copies duplicate data as well ......i only want distict data to be copied ..i tried dropping PK but on column eid but i'm facing same issue
sorry but the third ways not working for me . this the query that i use delete from (select nom, row_number() over(partition by nom order by nom ) as count_num from fourniseurs)as tab where count_num >1; but the select query work fine i wander why is it not working i use workbench mysql :/
delete from fourniseurs where nom IN (select nom from (select nom,row_number() over(partition by nom) as count_num from fourniseurs) ee where ee.count_num>1 );
Both of them won’t impact the actual table. 4th method is just removing the duplicates from the temp table, you can achieve by using a distinct query 😂. My guess for the 3rd query is it will remove both the duplicate and the actual record.
The row_number() methodology is not working in snowflake db . anyone help me on this, how to resolve this like if we have to mention some alias name aslo and instead of rn>1 if we have to write alias.rn>1
✨Link to my SQL e-book: imojo.in/rkajq9
✨Link to 'Switch your career easily' e-book: imojo.in/1FL7KaG
(Perfect for anyone willing to switch to a completely new field or to a different company in the Tech World)
I really appreciate your time and effort in sharing these (even though you are having cold)!! Thank you
Ya true..👍
That's called dedication...keep it up dear
Having cold ..hahaha..nailed it bro
Such developers actually reach to top positions, appreciate it.
Seen so many videos in SQL messed up ...this one really understandable with clear explanation!
explanation is very clear by writing in paper which is unique in youtube. great job
I have been stuck on this since yesterday. Thank you😎
Wow! You are so good teacher. Was able to understand it more than other videos with fancy animation.
Thank you for the clear explanations! You made the concepts simple to understand. I’m excited for new videos.
kitni awesome hai yaar... So well explained... calm and soothing...
It's a great post Sadia, no matter how many years of experience we have ,it's always a good revision for any one
# Additional Reference
There are also other ways to restrict the duplicates
1. UNIQUE
2. UNION
3. ROW_NUMBER | RANK () | DENSE_RANK () - Analytical Function
4. INTERSECT
5. MINUS
6. ROWID | ROWID + CORRELATED SUBQUERY
7. GROUP BY
Nice & simple way of understanding 👏👏 for new starters in sql ur videos are best .. thank you for ur efforts and time
Mam it would be great if you can take up topics or concepts which are really important like this though not much frequently asked in interviews and also it is useful for an engineer to know all these as well
Watching in 2022, for placements as being CSE student. Thanks a lot, didi. Your explanation is just amazing.
Very nice.simple understanding way you explain so nice
Your explanation is superb. one small request what ever you explained can you explain in sql server . that is very use full to us.
शेंबडाचा त्रास होत असताना देखील खूप छान शिकवलंस🙏👍 thank you
First method is easy to understand ...so I will go with the first method
First method will overload database performance. Avoid using select *
Nice explanation with such a calm voice.
Thank you 😃
Hello madam..
I have doubt in using temporary table..
Can insert distinct values in new table
Drop the old table
Rename new table as old table?
Learned best way to query thanks for the video !!
Thankyou so much. You've covered all the methods and discussed them in detail.
Simply superb, doing a great job, simple and clear
excellent explanation
very good explanation..
Thank you very much for you efforts... Must to be known things👌👌
Your efforts helped me a lot Thankyou mam
Excellent, your solutions are just great and easy to understand
your explanation is good. I am watching most of the videios.
for me 1 st method is showing error missing expression.
Very good explanation keep it up 👍
It's really helpful
over partition example was really helpful
SO SIMPLE AND EASY
Good explanation
Excellent explaination..
Thank you!
This was very helpful! Thank you so much
Absolutely brilliant explanation... Thank you so much..
a bit speedy but you're doing a great job!
Well explained, It will be most useful video in this topic..
3rd and 4th way will delete duplicate row from result set and not from actual table, now what to do for deleting duplicate rows from actual actual?
Excellent Explanation.....This video is really helpful.
Hii.. Please clear one question in this video ..In CTE inner query ..you have not used * after select keyword ..you have directly written " select row_number()..." .... * aaega na after select ??
Thanks for Helping
Thanks it is very important interview question
Very detailed description... thanks
Very well explained....Thank you 😀
Can we use the MAX(ID) method?
Very well explained
Explaining good & may be increase the volume (sound) better good
Thank a lots !!! :) Hope to see more videos from you.
Nice Explaination. You have a lovely voice
Well explained👍
You have amazing, simple.way of teaching.
God bless you
and
Thank you very much
Hi just one suggestion you first way will always not would the solution for routine. N in your second way we have to design table with identify type then you will be able to access
Your English is good
SUPER HELPFUL!
Helped me a lot Thanks 😁
Instead of MIN if we use Max then what will happen? I guess it will also remove duplicate
Clear explanation keep doing more videos👍
very good effort and I appreciate your clips. However I have a question; suppose application messed up and gave same ID to two (2) different unique valid name. eg. 5001 to ABC and 5001 to XYZ and we want to keep both that is they should exists in our resultant with 2 unique IDs. Please don't get me wrong I am not trying to trick you, I had this real situation once with thousands of rows and was stuck.
In that scenario, you need to add ID, name in partition.
DELETE FROM (
SELECT *, ROW_NUMBER() OVER (PARTITION BY ID, NAME ORDER BY ID) AS Rno
FROM emp
) AS E
WHERE Rno > 1;
This query will delete duplicate rows from the "emp" table where duplicates are determined by the combination of "ID" and "NAME" columns, keeping only the first occurrence and removing the rest.
So helpful thank you for sharing!
Nice video, and it would be great if you could make a video on types of Analytical Functions in sql, thanks!
So informative...👍
Good explanation. Can please explain and show by executing in the system.
Nyc video sister.
use insert into newtable select distinct * from oldtable; then delete * from oldtable; now insert into oldtable select * from newtable;
well Explained !! Thank you.
th-cam.com/video/zA9v9lnP2BE/w-d-xo.html
Ek different positive way me le gye aap lecture ko.. Wldone
Thanks for such stuff🥰
Madam derived table delete allow nahi karta he only u can use cte to delete duplicate record.
your videos are awesome please make more videos on different topics
What happen if there are more than 50 columns with the duplicate records
Instead of passing all the columns names to the partition by clause
Is there any way to put * .
Any thoughts?
Why we can't use direct delete query whose count greater than 1
Good Job!, Thank you.
thanks you explained well...
In third method : Can we access alias rn in outer query as well as use in where condition ?
It won't work.
Thank u dear..
ur voice is so nice😍
it will be really helpful , if you do this on your system, please,
it should be a live workout from your end, instead of explaining it on the written paper.
She will explain the way she wants to explain.You should be thanking her instead of showing flaws
Excelente muchas gracias
Hello mam, deleting using CTE throws the following error.... "Error Code: 1288. The target table cte of the DELETE is not updatable" how to resolve it?
Hi..one question i have ..pls rply ASAP... I'm using MYSQL and MYSQL doesn't support select * into.. therefore i used insert into newtable select distinct * from old table ...here i faced one issue...i hv one tble with eid,ename and salary. eid field is PK and ename and salary has duplicate values ...now when i use insert into using distinct keyword it copies duplicate data as well ......i only want distict data to be copied ..i tried dropping PK but on column eid but i'm facing same issue
Excellent madam pls do videos on power bi also pls
where rn>1 is not a good practice use having rn> 1 instead of it. Good video
I think row_number( ) works slower ... right? If you have millions of records then this will work slow.
Hello, I have a question, how to delete duplicate records with no unique key in mysql? Can this be done using the row_number function?
DELETE K FROM
(SELECT *, ROW_NUMBER () OVER (PARTITION BY emp_id ORDER BY emp_id) AS RN FROM MyTable) K
WHERE RN>1
The first method ,the same query will it work in Oracle sql
sorry but the third ways not working for me . this the query that i use
delete from
(select nom, row_number() over(partition by nom order by nom ) as count_num from fourniseurs)as tab where count_num >1;
but the select query work fine i wander why is it not working i use workbench mysql :/
delete from fourniseurs where nom IN (select nom from (select nom,row_number() over(partition by nom) as count_num from fourniseurs) ee where ee.count_num>1 );
Good work, thanks
INSERT INTO temp_table SELECT DISTINCT * FROM old_table;
so sNice tutorialt like that
Excellent explaination but Voice is little bit low.
What is the difference between delete and truncate?
superb...good job...
Thank you!
Very nice
THANKS
Will 3rd and fourth approach delete row from original table or it will just delete from temp table/result sets ? can you please clear my doubt
i also had the same doubt, but decided to insert into temporary table first and then do operations in a temporary table instread of original DB
Both of them won’t impact the actual table. 4th method is just removing the duplicates from the temp table, you can achieve by using a distinct query 😂. My guess for the 3rd query is it will remove both the duplicate and the actual record.
Please increase your sound.. Ur tone is low. But ur explanation is really good.
Great videos.
How come only distinct values will stored in new table if use -- Insert into old_table select * from new _table
? pls explain
because than new table has distinct values
Perfect. Thanks.
The row_number() methodology is not working in snowflake db . anyone help me on this, how to resolve this like if we have to mention some alias name aslo and instead of rn>1 if we have to write alias.rn>1