How to find and remove Duplicate values from table. Session 3
11.9 هزار بار بازدید -
11 ماه پیش
-
SQL Queries and Interview Questions
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)
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)
11 ماه پیش
در تاریخ 1402/06/22 منتشر شده
است.
11,920
بـار بازدید شده