CODEWITHSUNDEEP
sqlsql-servert-sql
Syntax Error
Syntax Error

Reputation: 1640

Selecting only those that have a duplicate row

I'm trying to find rows that have the same fnumber and fname

I can return the ones that are duplicated using this query but it only gives me the duplicates. I want to return both the duplicate row and the original.

WITH CTE AS(
   SELECT fnumber, findex,fname,
       RN = ROW_NUMBER()OVER(PARTITION BY fnumber ORDER BY fnumber)
   FROM dbo.coolTableBro
   where fnumber like '014%'
)
select * FROM CTE where RN > 1

Query output:

fnumber findex  fname   RN
01474   220569  MT1     2
01475   220570  MT1     2
01476   220571  MT1     2
01477   220572  MT1     2
01478   220573  MT1     2
01479   220574  MT1     2
01480   220575  MT1     2
01481   220576  MT1     2

Desired output:

fnumber findex  fname   RN
01474   220532  MT1     1
01474   220569  MT1     2
01475   220533  MT1     1
01475   220570  MT1     2
01476   220534  MT1     1
01476   220571  MT1     2
01477   220535  MT1     1
01477   220572  MT1     2
01478   220536  MT1     1
01478   220573  MT1     2
01479   220537  MT1     1
01479   220574  MT1     2
01480   220538  MT1     1
01480   220575  MT1     2
01481   220539  MT1     1
01481   220576  MT1     2

If I change the where clause I also get several rows that are not duplicate. I kind of need something like this pseudo statement, which obviously isn't valid SQL

select fnumber,findex,fname from coolTableBro where fnumber and fname are the same in at least two rows and fnumber starts with 014

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

One method uses count(*) instead of row_number():

with cte as (
      select fnumber, findex, fname,
             count(*) over (partition by fnumber) as cnt
      from dbo.coolTableBro
      where fnumber like '014%'
)
select * 
from cte
where cnt > 1;

In terms of performance, exists is often better:

select tb.*
from dbo.coolTableBro tb
where tb.fnumber like '014%' and
      exists (select 1
              from dbo.coolTableBro tb2
              where tb2.fnumber = tb.fnumber and tb2.findex <> tb.findex
             );

This is particularly true if you have an index on (fnumber, findex).

Upvotes: 0

Lukasz Szozda
Lukasz Szozda

Reputation: 175566

You could use COUNT:

WITH CTE AS(
   SELECT fnumber, findex,fname,
       RN = ROW_NUMBER()OVER(PARTITION BY fnumber ORDER BY fnumber),
       CNT = COUNT(*) OVER (PARTITION BY fnumber)
   FROM dbo.coolTableBro
   where fnumber like '014%'
)
select * FROM CTE where CNT > 1;

Upvotes: 2

Related Questions