Same in slightly different flavor

😎

USE tempdb;

GO

DECLARE @SAMPLE TABLE

(

PERSON VARCHAR(25) NOT NULL

,VAL_1 INT NOT NULL

,VAL_2 INT NOT NULL

);

INSERT INTO @SAMPLE (PERSON,VAL_1,VAL_2)

VALUES

('John',1,1)

,('John',1,1)

,('John',2,2)

,('John',3,3)

,('John',3,3)

,('John',4,4);

/* Nested query */

SELECT

X.PERSON

,X.VAL_1

,X.VAL_2

FROM

(

SELECT

S.PERSON

,S.VAL_1

,S.VAL_2

,COUNT(PERSON) OVER

(

PARTITION BY

S.PERSON

,S.VAL_1

) AS PCOUNT

FROM @SAMPLE S

) AS X

WHERE X.PCOUNT > 1

/* CTE version */

;WITH PBASE AS

(

SELECT

S.PERSON

,S.VAL_1

,S.VAL_2

,COUNT(PERSON) OVER

(

PARTITION BY

S.PERSON

,S.VAL_1

) AS PCOUNT

FROM @SAMPLE S

)

SELECT

PB.PERSON

,PB.VAL_1

,PB.VAL_2

FROM PBASE PB

WHERE PB.PCOUNT > 1