SQL Query to remove Duplicate values from table. Session 3

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)

[yogeshpandita]

It's really amazing to see you even covering small details. Thank you.

[naimful]

I really loved to watch this session of fetching and removing duplicate records. Thank you vaia.

[hanumanprasadvishwakarma]

without using aggregate function how we can use group by clause
it should give error

[utsavsagar]

Can we execute the beloq query?

Delete from person
Where email in(select email from person group by email
Having count(email)>1)

[chazc]

In mysql when trying to delete the duplicates, Im getting a message that says"table doesn't exist". How are you able to delete from a cte?