user2441297 - 6 months ago 45

SQL Question

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