:::: MENU ::::

How to get duplicate rows from a table

  • Jun 22 / 2009
  • 0
dbDigger, T-SQL Interview Questions, T-SQL Scripts, T-SQL Tips and Tricks

How to get duplicate rows from a table

In one of my previous articles, i discussed that how to remove duplicate rows from a table. While analyzing the duplicate rows based on some columns, often it is required to get whole list of columns even if they are not included in our duplicate criteria.
Instead of creating a table and populating it with data, i will simply go through the concept through a sample script only to get the list.

SELECT --comma separated list of all columns to select   
 FROM table1 a  
 (SELECT col1, col2, col3   
 FROM table1  
 GROUP BY col1, col2, col3   
 HAVING count(*) > 1) b   
 ON a.col1 = b.col1  
 and a.col2 = b.col2  
 and a.col3 = b.col3  
 ORDER BY a.col1, a.col2, a.col3  

Where col1, col2, col3 are columns on which duplicate rows are determined in table1.

Consult us to explore the Databases. Contact us