CODEWITHSUNDEEP
sqlsql-server
user2441297
user2441297

Reputation: 249

Count distinct duplicates

I have the following query:

WITH CTE AS(
   SELECT N, A, B,
   COUNT(*) OVER(PARTITION BY A, B) as cnt
   FROM Table
)
SELECT * FROM CTE WHERE cnt > 1

which is returning all duplicates count from Table. The output looks like this:

Table A (result of SQL)
N | A | B | cnt
----------------
1 | a | b | 2
1 | a | b | 2
1 | a | c | 1
1 | d | e | 2
2 | d | e | 2

And I now want to select duplicate values based on N (A & B the same, more than one distinct N), so:

Table B 
N | A | B | cnt
----------------
1 | d | e | 2
2 | d | e | 2

Should I just apply the SQL code above again on Table A to get Table B? Or is there any simpler solution?

Upvotes: 1

Views: 94

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269443

I would suggest:

WITH CTE AS (
      SELECT N, A, B,
             MIN(n) OVER (PARTITION BY A, B) as min_n,
             MAX(n) OVER (PARTITION BY A, B) as max_n
      FROM Table
     )
SELECT *
FROM CTE
WHERE min_n <> max_n;

Upvotes: 1

Related Questions