RE: [OT] Finding duplicate rows in Sql Server

Nov. 17, 2022

      Hi Tom,

Possible example:

DROP TABLE IF EXISTS #TestData;
GO

CREATE TABLE #TestData
(
    ID int NOT NULL,
    [Name] varchar(20) NOT NULL,
    [Description] varchar(30) NOT NULL,
    SomeDate date NOT NULL,
    Etc varchar(20) NOT NULL
);
GO

INSERT #TestData
(
    ID, [Name], [Description], SomeDate, Etc
)
SELECT ID, [Name], [Description], SomeDate, Etc
FROM (VALUES
      (1,'abc','abc abc','20221117','a'),
      (2,'abc','abc abc','20221117','a'),
      (5,'def','def def','20221117','a'),
      (4,'abc','abc abc','20221117','a'),
      (3,'def','def def','20221117','a'),
      (6,'xyz','def def','20221117','a')
     ) AS v(ID, [Name], [Description], SomeDate, Etc);
GO

SELECT * FROM #TestData;

WITH OrderedRows
AS
(
    SELECT ID, [Name], [Description], SomeDate, Etc,
           ROW_NUMBER() OVER(PARTITION BY [Name], [Description], SomeDate, Etc ORDER BY ID DESC) AS CopyNumber
    FROM #TestData
)
SELECT ID, [Name], [Description], SomeDate, Etc
FROM OrderedRows
WHERE CopyNumber > 1
ORDER BY ID;

DROP TABLE IF EXISTS #TestData;

Regards,

Greg

Dr Greg Low

1300SQLSQL (1300 775 775) office | +61 419201410 mobile
SQL Down Under | Web: https://sqldownunder.com<https://sqldownunder.com/> | About Greg:  https://about.me/greg.low

From: Tom P via ozdotnet <ozdotnet@ozdotnet.com>
Sent: Thursday, 17 November 2022 5:01 PM
To: ozDotNet <ozdotnet@ozdotnet.com>
Cc: Tom P <tompbiz93@gmail.com>
Subject: [OT] Finding duplicate rows in Sql Server

Apologies if this is basic for probably most of you but I just can't get my head around it.

I have a flat table in sql server which contains lots of duplicates, differing only by one column.

Id,Name,Desc,Date,Etc
1,abc,abc abc,2022-11-17,a
2,abc,abc abc,2022-11-17,a
5,def,def def,2022-11-17,a
4,abc,abc abc,2022-11-17,a
3,def,def def,2022-11-17,a
6,xyz,def def,2022-11-17,a

I'm trying to write a query that finds all duplicates excluding the ones with the highest Id. So for the above example it would return the following:

Id,Name,Desc,Date,Etc
1,abc,abc abc,2022-11-17,a
2,abc,abc abc,2022-11-17,a
3,def,def def,2022-11-17,a

There are many millions of rows to process so looking for something efficient. Any advice would be appreciated.

Regards
Tom