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)