SQL Server 2008 R2: Remove single occurrence record.

Question

I have the following table with two columns as shown below:

CREATE TABLE test_lin
(
    Cola INT,
    Colb INT
);

INSERT INTO test_lin VALUES(1,2);
INSERT INTO test_lin VALUES(1,3);
INSERT INTO test_lin VALUES(1,4);
INSERT INTO test_lin VALUES(1,5);
INSERT INTO test_lin VALUES(1,3);
INSERT INTO test_lin VALUES(2,4);
INSERT INTO test_lin VALUES(2,6);
INSERT INTO test_lin VALUES(2,7);
INSERT INTO test_lin VALUES(2,4);
INSERT INTO test_lin VALUES(2,6);

Note: Now I want to show only those records which are repeated more then once. Like in my case (1,3),(2,4),(2,6) records are repeated in the table.

I want to remove the single occurrence records from the result set. The records which are single occurrence are as shown below in the image.

Answer 1

WITH CTE as 
(
SELECT Cola,Colb,ROW_NUMBER() OVER(PARTITION BY Cola,Colb ORDER BY Cola) AS RN
FROM test_lin
)

SELECT T.* 
FROM test_lin T INNER JOIN  CTE C ON C.COLa = t.Cola and C.COLb = t.Colb and c.RN=2