![]() SQL Delete Duplicate Rows Using Common Table Expressions (CTE) Common Table Expression Select Name,Marks,grade,count(*) as cnt from stud group by Name,Marks,grade having count(*) > 1 Input: Under SQL, delete duplicate Rows in SQL is done with the Group by and Having clause. The table depicted above consisting of data repetition, that is duplicate data, can be deleted using the group by clause as follows: Deleting Duplicate Rows From the Table Using Group by and Having Clause * Display all the records from the table */ Code:Ĭreating and inserting data into the table:įirst, you need to create a table as follows:ĬREATE TABLE stud(Regno integer, Name text,Marks integer) įetching and Identifying the duplicate rows in SQL ![]() The COUNT function can be used to verify the occurrence of a row using the Group by clause, which groups data according to the given columns. The ROW NUMBER() function can be used in connection with a common table expression (CTE) to sort the data and then remove the duplicate records.Īlso Read: The Ultimate Guide on SQL Basics SQL Delete Duplicate Rows using Group By and Having ClauseĪccording to Delete Duplicate Rows in SQL, for finding duplicate rows, you need to use the SQL GROUP BY clause. ![]() Under Delete duplicate rows in SQL, it's a little difficult for tables without a special index. Self-joins, ordering the data by the maximum value, using the RANK function, or using NOT IN logic is used to perform identification. According to Delete duplicate rows in SQL, a table with a unique index can use the index to identify duplicate data, and then delete the duplicate records.
0 Comments
Leave a Reply. |