jason jason - 9 months ago 71
SQL Question

Remove duplicate entries without primary key in SQL

I have this table MSSQL :

EntityNo int
City nvarchar(MAX)
Province nvarchar(MAX)
EntityDate datetime

EntityNo should be unique, but it's not primary key. How can I remove one of the duplicate entries from this table? Thanks.

Answer Source

Use this query

    SELECT row_number() over(partition by EntityNo order by EntityNo) as RowNo
    FROM Entity
) AS a WHERE RowNo > 1