How to remove duplicate rows from a table

Example 1

Let's assume we have a #Students table and we want to DELETE the rows that are duplicated only considering the Name, Surname and Period columns.

CREATE TABLE #Students(
[Name] varchar(50),
[Surname] varchar(50),
[Period] int,
[Sport] int,
[History] int,
[English] int,
[Geography] int)

INSERT INTO
#Students([Name],[Surname],[Period],[Sport],[History],[English],[Geography])
VALUES
('Luke','Green',1,30,20,23,NULL),
('Luke','Green',1,30,20,23,NULL),
('Mary','Brown',1,17,15,NULL,30),
('John','Red',1,18,NULL,21,30),
('Walter','White',1,22,20,5,30),
('Luke','Green',2,30,20,23,NULL),
('Mary','Brown',2,NULL,15,17,30),
('John','Red',2,18,NULL,11,30),
('Walter','White',2,2,32,1,30),
('Luke','Green',3,20,15,15,12),
('Mary','Brown',3,0,3,NULL,4),
('Walter','White',2,2,32,1,30),
('Luke','Green',3,20,15,15,12),
('Mary','Brown',3,0,3,NULL,4),
('John','Red',3,18,40,21,30),
('Walter','White',3,17,19,15,30),
('Mary','Brown',1,17,15,NULL,30),
('John','Red',3,NULL,23,23,30);

WITH DupliTable AS(
   SELECT [Name], [Surname], [Period],
   ROW_NUMBER() OVER(PARTITION BY [Name], [Surname], [Period] ORDER BY [Name]) AS [rn]
   FROM #Students
)
DELETE FROM DupliTable WHERE [rn] > 1

You can find an interactive version of this example following this link .