CODEWITHSUNDEEP
sql-server
Shammas
Shammas

Reputation: 461

Delete duplicate records from SQL Server 2012 table with identity

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

Upvotes: 0

Views: 267

Answers (4)

Jay Wheeler
Jay Wheeler

Reputation: 379

DELETE s
FROM student s
JOIN student s2 ON s.course = s2.course
     AND s.NAME = s2.NAME
     AND s.rollno = s2.rollno
WHERE s2.id < s.id

Upvotes: 1

A_Sk
A_Sk

Reputation: 4630

you can Use DENSE_RANK, ROW_NUMBER ,RANK

these all'll give you the result.

Try:

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



;with cte as
(
  select rn=row_number() over
  (
     partition by rollno,name,course
    order by id
  ),*
  from student
)
select * from cte where rn=1

;with cte2 as
(
  select rn=RANK() over
  (
     partition by rollno,name,course
    order by id
  ),*
  from student
)
select *  from cte2 where rn=1

;with cte3 as
(
  select rn=Dense_RANK() over
  (
     partition by rollno,name,course
    order by id
  ),*
  from student
)
select *  from cte3 where rn=1

See Difference between ROW_NUMBER(), RANK() and DENSE_RANK()

Upvotes: 1

Mukesh Kalgude
Mukesh Kalgude

Reputation: 4844

It is simple query

Delete from student 
where id not in (select min(id) 
                 from student 
                 group by rollno, name, course)

Upvotes: 1

Ullas
Ullas

Reputation: 11556

Use CTE.

Query

;with cte as
(
  select rn=row_number() over
  (
     partition by rollno,name,course
    order by id
  ),*
  from student
)
delete from cte 
where rn > 1;

Fiddle demo

Upvotes: 4

Related Questions