user2441297 user2441297 - 3 months ago 16
SQL Question

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?

Answer

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;
Comments