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)

[@naim1234ful]

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

[@yogeshpandita7828]

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

[@sunnypandita1498]

So nice of you

[@nishapathania146]

Very nice explain sir

[@heemozero4035]

❤❤ thanks

[@vinothrajr1221]

Awesome explain

[@Mr.nikk62]

great Sir

[@hanumanprasadvishwakarma9273]

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

[@utsavsagar6665]

Can we execute the beloq query?
Delete from person
Where email in(select email from person group by email
Having count(email)>1)

[@sunnypandita1498]

Hi Sagar, thanks for your comment. With your query, it will delete duplicate data including original data also. But we need only duplicate data to get removed. There are several ways to get the same result. I had showed the one, there are many other simple ways too which can be used.

[@chazc080488]

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?

[@sunnypandita1498]

Hi, thanks for your comment. Table doesn't exist means there is no existing table. So, first need to create the table with same structure which is used in the session and next we need to execute CTE and next query together to perform the deletion..