I am trying to replicate a scenario where I need to delete all duplicate rows from a table except one. But all rows have a unique identity column.
For making things easier, I created a small test table student and the script is as below.
create table student
(
id int,
rollno int,
name varchar(50),
course varchar(50)
)
GO
insert into student values(1,1335592,'john','biology')
insert into student values(2,1335592,'john','biology')
insert into student values(3,1335592,'john','biology')
insert into student values(4,1335592,'john','biology')
insert into student values(5,1335593,'peter','biology')
insert into student values(6,1335593,'peter','biology')
insert into student values(7,1335593,'peter','biology')
GO
select * from student
This will generate the table as below.
id rollno name course
1 1335592 john biology
2 1335592 john biology
3 1335592 john biology
4 1335592 john biology
5 1335593 peter biology
6 1335593 peter biology
7 1335593 peter biology
I would like to keep the records with ID '1' and '5' in the result set and delete everything else. Is there any way to do this?.
All help will be greatly appreciated.
Thanks Shammas
It is simple query
Delete from student
where id not in (select min(id)
from student
group by rollno, name, course)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments