Eliminating Duplicates Based on a Single Column Using Window Function ROW_NUMBER()

Problem Background and Scenario Analysis

In database queries, scenarios often arise where duplicate records need to be eliminated based on specific columns. The case discussed here involves two tables: the sites table and the history table. The sites table contains site names (siteName) and IP addresses (siteIP), while the history table records site names (siteName) and update dates (date). The user's goal is to retrieve the latest historical record for each site via an inner join (INNER JOIN), i.e., deduplicate based on the siteName column and retain only the row with the most recent date in the date column.

Limitations of Traditional Methods

In SQL, a common approach for deduplication is the DISTINCT keyword. However, DISTINCT applies to the entire query result set and cannot deduplicate based on a single column while preserving the latest values from other columns. For example, the following query removes all duplicate rows but does not guarantee the latest date is retained:

SELECT DISTINCT sites.siteName, sites.siteIP, history.date
FROM sites INNER JOIN history ON sites.siteName = history.siteName
ORDER BY siteName, date

This may lead to inaccurate data, as DISTINCT randomly selects one row from duplicates, not necessarily the latest based on date sorting.

Core Application of Window Function ROW_NUMBER()

To address this, the best answer employs the window function ROW_NUMBER(). Window functions allow computations over subsets of the query result set (called "windows") without affecting the overall row count. Here is the optimized query code:

SELECT s.siteName, s.siteIP, h.date
FROM sites s INNER JOIN
     (SELECT h.*, ROW_NUMBER() OVER (PARTITION BY siteName ORDER BY date DESC) AS seqnum
      FROM history h
     ) h
    ON s.siteName = h.siteName AND seqnum = 1
ORDER BY s.siteName, h.date

Code Analysis:

• Subquery Part: In the history table, the ROW_NUMBER() function assigns a sequence number to rows within each siteName group (via PARTITION BY siteName), ordered by date in descending order (ORDER BY date DESC), ensuring the row with the latest date has a sequence number of 1.
• Join Condition: The main query uses INNER JOIN to connect the sites table with the subquery result, with join conditions matching siteName and seqnum = 1, thus selecting only the latest record for each siteName.
• Sorting: The final result is sorted by siteName and date for readability.

Performance and Scalability Analysis

The ROW_NUMBER() method outperforms alternatives like multiple subqueries or temporary tables, as it requires only a single scan of the history table. For large datasets, it is recommended to create indexes on the siteName and date columns to speed up sorting and grouping operations. Moreover, this method is easily extensible, e.g., by modifying the ORDER BY clause to filter records based on other criteria.

Conclusion

Using the window function ROW_NUMBER(), we can efficiently achieve deduplication based on a single column and filter for the latest records. This approach is not only code-concise but also performance-optimized, suitable for various SQL Server environments. In practice, developers should adjust flexibly based on data characteristics and query requirements to ensure data accuracy and query efficiency.