Is there any optimised way in sql sever to optimse this code, I am trying to find 2nd duplicate

Question

 WITH CTE AS    (
                     SELECT *,
                     ROW_NUMBER() OVER(PARTITION BY id,AN_KEY ORDER BY [ENTITYID]) AS [rn]
                     FROM [data].[dbo].[TRANSFER]
                      ) 
                        select *
                        INTO dbo.#UpSingle 
                        from CTE 
                        where RN=2

Does this query do what you want? – Gordon Linoff Feb 17 '17 at 12:58 — Gordon Linoff, Feb 17 '17 at 12:58

score 1 · Answer 1 · edited May 23 '17 at 12:09

1

UPDATE:

As GurV pointed out - this query doesn't solve the problem. It will only give you the items that have exactly two duplicates, but not the row where the second duplicate lies.

I am just going to leave this here from reference purposes.

Original Answer

Why not try something like this from another SO post: Finding duplicate values in a SQL table

SELECT
    id, AN_KEY, COUNT(*)
FROM
    [data].[dbo].[TRANSFER]
GROUP BY
    id, AN_KEY
HAVING 
    COUNT(*) = 2

I gather from your original SQL that the cols you would want to group by on are :

Id
AN_KEY

edited May 23 '17 at 12:09

Community

1
1

answered Feb 17 '17 at 12:22

Newteq Developer

1,558
19
31

2

Wrong. How is this finding the second duplicate row? – Gurwinder Singh Feb 17 '17 at 12:28
You're right, sorry, this doesn't get the duplicate row - it only returns the items that have exactly two occurrences – Newteq Developer Feb 17 '17 at 12:41

Gurwinder Singh · Answer 2 · 2017-02-17T13:07:19.293

Here is another way to get the the second duplicate row (in the order of increasing ENTITYID of course):

select *
from [data].[dbo].[TRANSFER] a
where [ENTITYID] = (
        select min([ENTITYID])
        from [data].[dbo].[TRANSFER] b
        where [ENTITYID] > (
                select min([ENTITYID])
                from [data].[dbo].[TRANSFER] c
                where b.id = c.id
                    and b.an_key = c.an_key
                )
            and a.id = b.id
            and a.an_key = b.an_key
        )

Provided there is an index on id, an_key and ENTITYID columns, performance of both your query and this should be acceptable.

score 0 · Answer 3 · answered Feb 17 '17 at 12:58

Let me assume that this query does what you want:

WITH CTE AS  (
      SELECT t.*,
             ROW_NUMBER() OVER (PARTITION BY id, AN_KEY
                                ORDER BY [ENTITYID]) AS [rn]
      FROM [data].[dbo].[TRANSFER] t
     ) 
SELECT *
INTO dbo.#UpSingle 
FROM CTE 
WHERE RN = 2;

For performance, you want a composite index on [data].[dbo].[TRANSFER](id, AN_KEY, ENTITYID).

Is there any optimised way in sql sever to optimse this code, I am trying to find 2nd duplicate

3 Answers3