remove duplicate rows

Question

We have a big table with thousands of rows. There are some duplicate lines. Is there a suitable way to help us remove these duplicate rows without moving other non-duplicate rows?

Any ideas or tutorials? Thank.

Answer 1

Hi @Potter123

You can refer to this document.

https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/

If you still have questions after reading the documentation, I suggest that you show some of the data so that we can design targeted deletion statements.

Best regards,

Percy Tang

Answer 2

We have a big table with thousands of rows.

Thousands of records is not big, it's very small.

Anyway, without knowing the table design and how you define "duplicate", we can not suggest.

Please post table design as DDL, some sample data as DML statement and the expected result.

Answer 3

You can write queries that will only return unique rows.

There are other strategies as well. For example you can create a blank table with a unique clustered index with ignore_dup_key option set. Then insert into that table from main table and duplicate rows will be filtered out.

Rgds.

Answer 4

A common way to remove duplicates is this:

; WITH numbering AS (
    SELECT *, rowno = row_number() OVER(PARTITION BY col1, col2, col3 ORDER BY col4 DESC)
    FROM tbl
)
DELETE numbering WHERE rowno >1

In the PARTITION BY clause you list the column you wan to be unique after the cleanup. In the ORDER BY clause you select which of these rows you pick. For instance, col4 could be a timestamp column, and you want the most recent column. (Which is why I have DESC in my example.

Answer 5

Thanks all!