> For the complete documentation index, see [llms.txt](https://kythuat.dtechvn.com/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://kythuat.dtechvn.com/sql-server/tim-ban-ghi-trung-tren-bang.md).

# Tìm bản ghi trùng trên bảng

## TÌM CÁC BẢN GHI

**Cách 1:**

```sql
SELECT a.*
FROM users a
JOIN (SELECT username, email, COUNT(*)
FROM users 
GROUP BY username, email
HAVING count(*) > 1 ) b
ON a.username = b.username
AND a.email = b.email
ORDER BY a.email
```

**Cách 2:**

```sql
WITH customerdata AS (
    SELECT 
        CustomerID,
        FirstName, 
        LastName, 
        CompanyName,
        ROW_NUMBER() OVER (
        PARTITION BY FirstName,LastName
        ORDER BY FirstName, LastName
            ) As Occurrences
    FROM 
        [SalesLT].[Customer]
) 
SELECT 
  * 
FROM 
    customerdata 
WHERE 
    Occurrences > 1;
```

## XOÁ CÁC BẢN GHI

Cách 1:

```sql
DELETE FROM [SampleDB].[dbo].[Employee]
    WHERE ID NOT IN
    (
        SELECT MAX(ID) AS MaxRecordID
        FROM [SampleDB].[dbo].[Employee]
        GROUP BY [FirstName], 
                 [LastName], 
                 [Country]
    );
```

Cách 2:

```sql
WITH CTE([FirstName], 
    [LastName], 
    [Country], 
    DuplicateCount)
AS (SELECT [FirstName], 
           [LastName], 
           [Country], 
           ROW_NUMBER() OVER(PARTITION BY [FirstName], 
                                          [LastName], 
                                          [Country]
           ORDER BY ID) AS DuplicateCount
    FROM [SampleDB].[dbo].[Employee])
DELETE FROM CTE
WHERE DuplicateCount > 1;


--Hoặc dùng rank

DELETE E
    FROM [SampleDB].[dbo].[Employee] E
         INNER JOIN
    (
        SELECT *, 
               RANK() OVER(PARTITION BY firstname, 
                                        lastname, 
                                        country
               ORDER BY id) rank
        FROM [SampleDB].[dbo].[Employee]
    ) T ON E.ID = t.ID
    WHERE rank > 1;
```

{% embed url="<https://www.sqlshack.com/different-ways-to-sql-delete-duplicate-rows-from-a-sql-table/>" %}

{% embed url="<https://www.c-sharpcorner.com/article/how-to-delete-duplicate-rows-from-a-table-in-sql-server/>" %}