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 join (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 GO
Where col1, col2, col3 are columns on which duplicate rows are determined in table1.