How to delete duplicate rows in SQL Server?

How to Delete Duplicate Rows in SQL Server? 💥

Have you ever encountered a situation where your SQL Server table has duplicate rows and you're scratching your head trying to figure out how to remove them? 😫 Don't worry, we've got you covered!

The Problem 🤔

Let's say you have a table with multiple columns and duplicate rows, as shown below:

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

Your goal is to remove the duplicate rows and retain only unique rows, like this:

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

The Solution 😎

Here's a simple SQL query that will do the trick, even if you don't have a unique row id:

WITH CTE AS (
  SELECT 
    col1, col2, col3, col4, col5, col6, col7,
    ROW_NUMBER() OVER (
      PARTITION BY col1, col2, col3, col4, col5, col6, col7
      ORDER BY (SELECT 0)
    ) AS RowNumber
  FROM 
    YourTable
)
DELETE FROM CTE WHERE RowNumber > 1;

Let's break it down a bit:

1. We create a Common Table Expression (CTE) using the WITH keyword. This allows us to perform the deletion in a single query.

2. The CTE selects all columns from the table and adds a new column called RowNumber.

3. The ROW_NUMBER() function is used to assign a sequential number to each row within a specific set of columns. In this case, we're partitioning the rows based on all columns to identify duplicates.

4. The PARTITION BY clause specifies the columns to partition by, while the ORDER BY clause ensures that the order of the rows is consistent.

5. Lastly, we delete all rows where the RowNumber is greater than 1, effectively removing the duplicates.

And voila! Your duplicate rows are gone! 🎉

The Call-To-Action 📢

Now that you know how to delete duplicate rows in SQL Server, why not put your newly acquired knowledge to the test? Try it out on your own tables and let us know your success stories! If you have any questions or other SQL-related problems, feel free to drop a comment below.

Happy coding! 💻😄