Скачать с ютуб SQL Query to remove Duplicate values from table. Session 41 в хорошем качестве

SQL Query to remove Duplicate values from table. Session 41

SQL Queries and Interview Questions  Get duplicate data  Remove duplicate data  The GROUP BY statement groups rows that have the same values into summary rows.  A Common Table Expression, also called as CTE in short form, is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement that immediately follows the CTE. The CTE can also be used in a View. -------------------------------------------------------------------------------------------------------------------------------------------------- CREATE TABLE [customer3]( [id] [int] IDENTITY(1,1) NOT NULL, [name] [varchar](20) NULL, [place] [varchar](30) NULL, [gender] [char](10) NULL, [salary] [int] NULL ) -------------------------------------------------------------------------------------------------------------------------------------------------- insert into customer3 values('sunny', 'noida', 'male', 1000) insert into customer3 values('rahul', 'delhi', 'male', 2000) insert into customer3 values('mahesh', 'goa', 'male', 3000) insert into customer3 values('suresh', 'USA', 'male', 4000) ---------------------------------------------------------------------------------------------------------------------------------------------------- with test as ( select name, place,gender, row_number() over(partition by name, place,gender order by id) as rownumber from customer3 ) select * from ctename where rownumber (GreaterThanSign) 1 delete from test where rownumber (GreaterThanSign) 1 --------------------------------------------------------------------------------------------------------------------------------------------------- select name, place, gender, count(*) as rownumber from customer3 group by name, place, gender having count(*) (GreaterThanSign )1 delete from customer3 where id not in(select min(id) from customer3 group by name, place, gender)