How to delete duplicate rows in SQL Server

0 votes

How will I be able to delete the duplicate rows where no unique row id exists?

My table is

col1 col2 col3 col4 col5 col6 col7 
john 1 1 1 1 1 1 
john 1 1 1 1 1 1 
sally 2 2 2 2 2 2 
sally 2 2 2 2 2 2

I want to be left with the following after the duplicate removal:

john 1 1 1 1 1 1 
sally 2 2 2 2 2 2

I also tried a few queries but I think they depend on having a row id as I don't get the desired result. For example:

DELETE 
FROM table 
WHERE col1 IN ( 
          SELECT id 
          FROM table 
          GROUP BY id 
          HAVING (COUNT(col1) > 1)
)
Feb 10 in Others by Rahul
• 8,980 points
62 views

1 answer to this question.

0 votes

To answer your query, note that CTEs and ROW_NUMBER are the both combined, which allow us to see which rows are deleted and hence, just make the change to the DELETE FROM CTE... to SELECT * FROM CTE:

WITH CTE AS( 
      SELECT [col1], [col2], [col3], [col4], [col5], [col6], [col7], 
                RN = ROW_NUMBER()OVER(PARTITION BY col1 ORDER BY col1) 
      FROM dbo.Table1 
) 

DELETE FROM CTE WHERE RN > 1

The DEMO result is different and hence, I assume that it's due to a typo on your part.
 

COL1 COL2 COL3 COL4 COL5 COL6 COL7 
john 1 1 1 1 1 1 
sally 2 2 2 2 2 2

This example determines duplicates by a single column col1 because of the PARTITION BY col1. If you want to include multiple columns simply add them to the PARTITION BY:


ROW_NUMBER()OVER(PARTITION BY Col1, Col2, ... ORDER BY OrderColumn)
answered Feb 10 by Soham
• 8,730 points

Related Questions In Others

0 votes
1 answer

How to import excel file in Oracle SQL live

Hello, there are a few steps You'll ...READ MORE

answered Feb 18 in Others by Edureka
• 8,820 points
120 views
0 votes
2 answers

How to get the URL of the current tab in Google Chrome?

Its so simple.... If you want to ...READ MORE

answered Aug 12, 2020 in Others by Steve
• 200 points
613 views
0 votes
1 answer

What is a name function in JavaScript & how to define it?

A named function declares a name as ...READ MORE

answered Mar 7, 2019 in Others by Frankie
• 9,830 points
702 views
0 votes
1 answer

How to delete duplicate rows in SQL Server?

CTEs and ROW_NUMBER can be combined together which will ...READ MORE

answered Feb 10 in Database by Vaani
• 5,040 points
18 views
0 votes
1 answer

How to duplicate IDs with Hive QL / Impala / Python

Based on your example, supposing id2 always ...READ MORE

answered Oct 1, 2018 in Python by Priyaj
• 58,060 points
252 views
0 votes
1 answer

Rename column SQL Server 2008

Use sp_rename EXEC sp_RENAME 'TableName.PreviousColumnName' , 'NewColumnName', 'COLUMN' See: SQL SERVER ...READ MORE

answered Feb 23 in Database by Vaani
• 5,040 points
16 views
0 votes
1 answer

Which query to use for better performance, join in SQL or using Dataset API?

DataFrames and SparkSQL performed almost about the ...READ MORE

answered Apr 19, 2018 in Apache Spark by kurt_cobain
• 9,390 points
932 views
0 votes
1 answer

How to take a screenshot of a current Activity and then share it?

For me, I captured and then shared ...READ MORE

answered Feb 8 in Others by Soham
• 8,730 points
13 views
0 votes
1 answer

Unable to detect adb version, adb output in Android Studio 3.3.2

To avoid this ERROR, start with changing ...READ MORE

answered Feb 11 in Others by Soham
• 8,730 points
244 views
webinar REGISTER FOR FREE WEBINAR X
Send OTP
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP