How To Select Sequential Duplicates In Sql Server

February 16, 2024 Post a Comment

I would like to select duplicate entries from a SQL Server table, but only if the id is consecutive. I have been trying to twist this answer to my needs, but I can't get it to wor

Solution 1:

You are very close to what you want:

select id, companyName
from (select c.*,
             lag(companyName, 1) over (orderby id) prev,
             lead(companyName, 1) over (orderby id) nextfrom companies c
     ) a
where CompanyName in (prev, next)
orderby id;

Solution 2:

This is a gaps and islands style problem, but instead of using two row_numbers(), we use the id and row_number() in the innermost subquery. Followed by count() over() to get the count per grp, and finally return those with a cnt > 1.

select id, companyname 
from (
  select 
      id
    , companyName
    , grp
    , cnt =count(*) over (partitionby companyname, grp)
  from (
    select*
      , grp = id -row_number() over (partitionby companyname orderby id)
    from
      companies
    ) islands
  ) d
where cnt  >1orderby id

rextester demo: http://rextester.com/ACP73683

returns:

+----+-------------+
| id | companyname |
+----+-------------+
|  3 | pigs ltd    |
|  4 | pigs ltd    |
|  5 | cats ltd    |
|  6 | cats ltd    |
+----+-------------+

Solution 3:

One more alternate form, using LEAD() and LAG() (SQL 2012 and up)

SELECT id, CompanyName
FROM (
    SELECT*,
        LEAD(CompanyName, 1) OVER(ORDERBY id) as nc,
        LAG(CompanyName, 1) OVER(ORDERBY id) AS pc
    FROM #t t
    ) x
WHERE nc = companyName
    OR pc = companyName

Here is the test data, so you can check it out yourself.

CREATETABLE #T (id intnotnullPRIMARY KEY, companyName varchar(16) notnull)

INSERTINTO #t Values 
(1,       'dogs ltd'),
(2,       'cats ltd'),
(3,       'pigs ltd'),
(4,       'pigs ltd'),
(5,       'cats ltd'),
(6,       'cats ltd'),
(7,       'dogs ltd'),
(8,       'pigs ltd')

Solution 4:

In the WHERE clause you just need to limit to those where the companyName is the same as the prev or the next

select id, companyName
from (
   select id, companyName,
   lag(companyName, 1) over (orderby id) as prev,
   lead(companyName, 1) over (orderby id) asnextfrom companies
 ) q
 where companyName in (prev, next)
 orderby id;

To make sure the id's are really without gaps then you can do it like this:

select id, companyName
from (
   select id, companyName,
   lag(concat(id+1,companyName), 1) over (orderby id) as prev,
   lead(concat(id-1,companyName), 1) over (orderby id) asnextfrom companies
 ) q
 where concat(id,companyName) in (prev, next)
 orderby id;

Solution 5:

You can use Row_Number() and get the duplicates based on partition by clause

;with cte as (
SELECT id, companyName,
    RowN =Row_Number() over (partitionby id orderby companynae) from #yourTable
    )
    Select*from cte where RowN >1

Can you provide your input and expected output to verify this query

Lab Sql