How to Find Duplicate Records in SQL Server Using GROUP BY and ROW_NUMBER

Duplicate records can make import reports misleading and reconciliation totals difficult to trust. Before deciding what to keep, you need a reliable way to identify repeated data.

This tutorial shows how to find duplicate records in SQL Server with GROUP BY, HAVING and ROW_NUMBER(). You will build sample data, identify repeated groups and inspect the individual rows behind them.

First define what counts as a duplicate

Two rows are duplicates only under a rule you have chosen. Matching transaction dates and amounts alone does not establish that two transactions are the same: different payments may have identical amounts on the same day.

In this demonstration, the candidate duplicate key is SourceRef + TransactionDate + Amount. The unique RowID identifies each stored row. In your application, include the necessary account, source, branch, direction or other business identifiers. The example key is illustrative, not a universal banking rule.

Create sample data in SQL Server

Open a query window in SQL Server Management Studio and run this script. The temporary table is scoped to your session. Use the same query window for the remaining examples; run the setup once.

CREATE TABLE #ImportDemo
(
    RowID int NOT NULL PRIMARY KEY,
    SourceRef varchar(20) NOT NULL,
    TransactionDate date NOT NULL,
    Amount decimal(18,2) NOT NULL
);

INSERT INTO #ImportDemo
    (RowID, SourceRef, TransactionDate, Amount)
VALUES
    (1, 'REF-1001', '20260901', 1500.00),
    (2, 'REF-1001', '20260901', 1500.00),
    (3, 'REF-1002', '20260901', 1500.00),
    (4, 'REF-1003', '20260902', 2000.00),
    (5, 'REF-1003', '20260902', 2000.00),
    (6, 'REF-1003', '20260902', 2000.00);

There are six rows: REF-1001 appears twice, REF-1002 appears once, and REF-1003 appears three times. REF-1002 has the same date and amount as REF-1001 but a different reference, so it stays separate under our chosen rule.

Method 1: Find duplicate groups with GROUP BY and HAVING

Use this query when you want a compact summary showing which keys repeat and how many rows each key has.

SELECT SourceRef, TransactionDate, Amount,
       COUNT(*) AS RecordCount
FROM #ImportDemo
GROUP BY SourceRef, TransactionDate, Amount
HAVING COUNT(*) > 1
ORDER BY SourceRef, TransactionDate, Amount;

SourceRef	TransactionDate	Amount	RecordCount
REF-1001	2026-09-01	1500.00	2
REF-1003	2026-09-02	2000.00	3

GROUP BY creates one result per key combination. HAVING keeps groups whose count exceeds one. Use WHERE to restrict input rows before grouping, and HAVING to filter grouped results. See Microsoft's HAVING reference.

Method 2: Identify extra rows with ROW_NUMBER()

A grouped report does not show every stored RowID. Use a common table expression and ROW_NUMBER() to number rows inside each candidate duplicate group.

;WITH RankedRows AS
(
    SELECT RowID, SourceRef, TransactionDate, Amount,
           ROW_NUMBER() OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
               ORDER BY RowID
           ) AS DuplicateNumber,
           COUNT(*) OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
           ) AS GroupCount
    FROM #ImportDemo
)
SELECT RowID, SourceRef, TransactionDate, Amount,
       DuplicateNumber, GroupCount
FROM RankedRows
WHERE DuplicateNumber > 1
ORDER BY SourceRef, TransactionDate, Amount, RowID;

RowID	SourceRef	DuplicateNumber	GroupCount
2	REF-1001	2	2
5	REF-1003	2	3
6	REF-1003	3	3

The table above shows selected columns from the result. PARTITION BY defines each group. ORDER BY RowID places its lowest RowID first. Filtering DuplicateNumber > 1 returns the remaining rows.

The ordering must resolve ties if you need repeatable row selection. RowID is a unique primary key here. Ordering only by the shared transaction date would leave tied rows without a stable choice. Microsoft's ROW_NUMBER documentation explains ordering and uniqueness requirements.

Show all rows that belong to duplicate groups

To review the first row alongside its repetitions, change the filter to GroupCount > 1:

;WITH RankedRows AS
(
    SELECT RowID, SourceRef, TransactionDate, Amount,
           ROW_NUMBER() OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
               ORDER BY RowID
           ) AS DuplicateNumber,
           COUNT(*) OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
           ) AS GroupCount
    FROM #ImportDemo
)
SELECT RowID, SourceRef, TransactionDate, Amount,
       DuplicateNumber, GroupCount
FROM RankedRows
WHERE GroupCount > 1
ORDER BY SourceRef, TransactionDate, Amount, RowID;

This returns RowIDs 1, 2, 4, 5 and 6. RowID 3 is excluded because its group contains only one row.

Return one representative row per group

For a report that needs one row per key, use DuplicateNumber = 1:

;WITH RankedRows AS
(
    SELECT RowID, SourceRef, TransactionDate, Amount,
           ROW_NUMBER() OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
               ORDER BY RowID
           ) AS DuplicateNumber,
           COUNT(*) OVER
           (
               PARTITION BY SourceRef, TransactionDate, Amount
           ) AS GroupCount
    FROM #ImportDemo
)
SELECT RowID, SourceRef, TransactionDate, Amount,
       DuplicateNumber, GroupCount
FROM RankedRows
WHERE DuplicateNumber = 1
ORDER BY SourceRef, TransactionDate, Amount, RowID;

This returns RowIDs 1, 3 and 4. It changes the query result; it does not remove any records from the table. Also, the lowest ID is only a selection rule. It does not prove that row is the correct business record.

Common mistakes when checking duplicates

Grouping by the unique RowID: Every row becomes a separate group, hiding repeated business values.
Confusing groups with extra rows: Our sample has two duplicate groups, five rows in those groups and three extra rows.
Comparing timestamps as dates: A datetime column includes time. If your rule means calendar day, group consistently by CAST(TransactionDate AS date); if time matters, retain it.
Using incomplete identifiers: Add account or source identifiers when the same reference can occur in multiple contexts.
Ignoring text and missing-value rules: SQL Server collation affects text comparisons. Define how to treat case, whitespace and NULL values before designing a duplicate key.
Using DISTINCT as a repair: DISTINCT affects selected output columns. It does not explain which stored rows repeated or prevent another duplicate import.

How to prevent repeated imports

Once the correct business key is established, consider a UNIQUE constraint or unique index that enforces it. An application-level existence check alone can race with another writer. Inspect existing duplicates and confirm key semantics before adding the database constraint.

Some repeating date-and-amount combinations are legitimate and must remain allowed. Never add a unique rule simply because a reporting query groups those columns.

Frequently asked questions

Should I use GROUP BY or ROW_NUMBER?

Use GROUP BY for repeated-key counts. Use ROW_NUMBER when you need individual row identifiers or one representative per group.

Will these queries delete duplicate records?

No. They inspect data and return different views of the same sample. Any cleanup requires a separate decision about which records should remain.

Why does my query return no duplicates?

Check the grouping columns and any date filters. Including a unique identifier in the duplicate key prevents rows from sharing that key.

Conclusion

Start with a precise duplicate definition, summarize repeated keys with GROUP BY, and inspect their rows with ROW_NUMBER. That gives you a reviewable result before changing import behavior or enforcing a database rule.