# 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/>" %}


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://kythuat.dtechvn.com/sql-server/tim-ban-ghi-trung-tren-bang.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.