In this article we will see how to delete duplicate records by using
Comman table expression : and Row_number() function in sql server.
Example:
step1: Create a table
CREATE TABLE #TEMP
(
id int
,city VARCHAR(50)
,sNAME VARCHAR(50)
)
step2: insert data into table
INSERT INTO #TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')
step3:: use below query for delete a duplicate records from your table
WITH CTE AS
(
select id,city,SNAME, row_number()
over(partition by SNAME order byid desc)
duplicate_id from #TEMP
)
deleteCTE where duplicate_id>1
select * from #temp
explaination::
I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for select duplicate cities
In This article I have explain how to delete duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion
,Thanks
Comman table expression : and Row_number() function in sql server.
Example:
step1: Create a table
CREATE TABLE #TEMP
(
id int
,city VARCHAR(50)
,sNAME VARCHAR(50)
)
step2: insert data into table
INSERT INTO #TEMP (id,city,SNAME)VALUES(1,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(2,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(3,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(4,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(5,'sangli','SANDIPg')
INSERT INTO #TEMP (id,city,SNAME)VALUES(6,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(7,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(8,'kolhapur','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(9,'mumbai','SANDIP')
INSERT INTO #TEMP (id,city,SNAME)VALUES(10,'mumbai','SANDIP')
step3:: use below query for delete a duplicate records from your table
WITH CTE AS
(
select id,city,SNAME, row_number()
over(partition by SNAME order byid desc)
duplicate_id from #TEMP
)
deleteCTE where duplicate_id>1
select * from #temp
explaination::
I have used a row_number() function for numbering a duplicate records.
for your practice you can try same query for select duplicate cities
In This article I have explain how to delete duplicate records from table by using
row_number() function and comman table expression.
i hope it is helpfull to you ,if it is then give me a comments ,advice ,your questions,and
suggestion
,Thanks
I need query for same scenario with out using windows functions. Thank You in advance..!!!
ReplyDelete