Geek Answers Handbook

SQL query to find duplicate rows in any table

I am looking for a schema independent request. That is, if I have a table usersor table purchases, the query should be equally able to catch duplicate rows in any table without any changes (except for the sentence from, of course).

I am using T-SQL, but I am assuming there should be a general solution.

+5
source share
4 answers

I believe this should work for you. Keep in mind that CHECKSUM () is not 100% perfect - it is theoretically possible to get a false result here (I think), but otherwise you can just change the table name and this should work:

;WITH cte AS (
    SELECT
        *,
        CHECKSUM(*) AS chksum,
        ROW_NUMBER() OVER(ORDER BY GETDATE()) AS row_num
    FROM
        My_Table
)
SELECT
    *
FROM
    CTE T1
INNER JOIN CTE T2 ON
    T2.chksum = T1.chksum AND
    T2.row_num <> T1.row_num

ROW_NUMBER() , . ORDER BY , GETDATE() .

CTE, .

+8

CTE SQL Server.

, , , :

WITH CTE (COl1, Col2, DuplicateCount)
AS
(
    SELECT COl1,Col2,
    ROW_NUMBER() OVER(PARTITION BY COl1,Col2 ORDER BY Col1) AS DuplicateCount
    FROM DuplicateRcordTable
)
DELETE
FROM CTE
WHERE DuplicateCount > 1
GO

, SQL:

http://blog.sqlauthority.com/2009/06/23/sql-server-2005-2008-delete-duplicate-rows/

+1

, " ", .

.

SELECT DISTINCT * FROM USERS

, , - ,

SELECT 
   [Each and every field]
FROM
   USERS
GROUP BY
   [Each and every field]
HAVING COUNT(*) > 1

(*), GROUP BY * .

,

SQL sys.columns information_schema.columns

DECLARE @colunns nvarchar(max)
SET  @colunns = ''

SELECT @colunns = @colunns  + '[' +  COLUMN_NAME  +'], ' 
FROM INFORMATION_SCHEMA.columns  
WHERE table_name = 'USERS'

SET  @colunns  = left(@colunns,len(@colunns ) - 1)


DECLARE @SQL nvarchar(max)
SET @SQL = 'SELECT '  + @colunns 
          + 'FROM  USERS' + 'GROUP BY ' 
          + @colunns 
           + ' Having Count(*) > 1'


exec sp_executesql @SQL

, SQL,

+1

. SQL. . sys.

/*
This SP returns all duplicate rows (1 line for each duplicate) for any given table.

to use the SP:
exec [database].[dbo].[sp_duplicates] 
    @table = '[database].[schema].[table]'  

*/
create proc dbo.sp_duplicates @table nvarchar(50) as

declare @query nvarchar(max)
declare @groupby nvarchar(max)

set @groupby =  stuff((select ',' + [name]
                FROM sys.columns
                WHERE object_id = OBJECT_ID(@table)
                FOR xml path('')), 1, 1, '')

set @query = 'select *, count(*)
                from '+@table+'
                group by '+@groupby+'
                having count(*) > 1'

exec (@query)
0

More articles:


All Articles