How to use SQL query to delete duplicate rows
In this tutorial SQL interview questions and answers. We will discuss how to use SQL query to delete duplicate rows except one from a SQL server table this is a very common interview question it is the table with duplicate data record notice that Markers record is duplicated three times pencil
record three times and whitepaper are two times in the table.
create table stationery(
ID int,
Product_name nvarchar(50),
items_color nvarchar(50),
prices int
)
Go
Now we delete the query that we are going to write should delete all the duplicate rows except one original record of stationery means after.
we execute the delete query the stationery table should be left with just three records as you can see here let's see how to achieve this. Let's enter to the SQL server management studio 2018.
so we have created a stationery table in SQL server management studio.
and execute the script and test that can do it. In case you need it now to copy the script from the above query.
insert into stationery values(1,'Markers','red',50);
insert into stationery values(1,'Markers','red',50);
insert into stationery values(1,'Markers','red',50);
insert into stationery values(2,'Pens','Muti-colurs',250);
insert into stationery values(3,'Pencil','charcoal',90);
insert into stationery values(3,'Pencil','charcoal',90);
insert into stationery values(3,'Pencil','charcoal',90);
insert into stationery values(4,'White paper','white',10);
insert into stationery values(4,'White paper','white',10);
with stationeryCTE As
(
select * ,ROW_NUMBER() over(Partition BY ID order BY ID) as ROWNUMBER
from stationery
)
select * from stationeryCTE;
And we going to make use of common table expressions and Let's call these common table expressions call it has "stationerysample" .
We are going to select all the columns from the stationery table along with all the columns and we also want the row number function make to use and partition by ID columns and then column order the data by ID column and then based on that generate items separated by RowNumber 1,2,3 and call it has row number and so first of all these first three records data partitioned by Id and then the row numbers are unique with that partition and we can very easily delete all the rows excepts one in the delete query.
with stationeryCTE As
(
select * ,ROW_NUMBER() over(Partition BY ID order BY ID) as ROWNUMBER
from stationery
)
Delete from stationeryCTE WHERE ROWNUMBER > 1;
we can use this rule number as the filter so instead of selecting all the rows from employees what we can do is delete from employees where the row number column is greater than one okay. so what is it going to do it's going to delete anything that has got row number > 1. It's going to leave just one record on the stationery table. and "select * from stationery" and execute the query result is set by ID column and leave the record only one.
SELECT * FROM stationery;
