How to delete duplicate rows in SQL Server

0 votes

How can I delete 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've 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)
)
Jun 20 in Others by polo
• 1,500 points
40 views

1 answer to this question.

0 votes
WITH CTE AS
(
SELECT *,ROW_NUMBER() OVER (PARTITION BY col1,col2,col3 ORDER BY col1,col2,col3) AS RN
FROM MyTable
)

DELETE FROM CTE WHERE RN<>1

without keeping original

WITH CTE AS
(SELECT *,R=RANK() OVER (ORDER BY col1,col2,col3)
FROM MyTable)
 
DELETE CTE
WHERE R IN (SELECT R FROM CTE GROUP BY R HAVING COUNT(*)>1)
answered Jun 21 by nisha
• 2,180 points

Related Questions In Others

0 votes
1 answer

How to delete duplicate rows in SQL Server?

To answer your query, note that CTEs ...READ MORE

answered Feb 10 in Others by Soham
• 9,330 points
189 views
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 gaurav
• 18,960 points
391 views
0 votes
1 answer

How do I UPDATE from a SELECT in SQL server?

In SQL Server, it is possible to insert ...READ MORE

answered May 30 in Others by anisha
• 140 points
32 views
0 votes
1 answer

How can we UPDATE from a SELECT in an SQL Server

We can firstly use SELECT statement to fetch ...READ MORE

answered May 27 in Others by Avinash
• 240 points
64 views
0 votes
1 answer

How Can I use "Date" Datatype in sql server?

Your dates are interpreted as MM-DD-YYYY. This ...READ MORE

answered Jun 21 in Others by nisha
• 2,180 points
46 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
961 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
1,242 views
0 votes
1 answer

Convert Rows to columns using 'Pivot' in SQL Server

If you are using SQL Server 2005+, ...READ MORE

answered Jun 20 in Others by nisha
• 2,180 points
131 views
0 votes
1 answer

How to generate the "create table" sql statement for an existing table in postgreSQL

Create a table for a demo: CREATE TABLE ...READ MORE

answered Jun 20 in Others by nisha
• 2,180 points
38 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