SQL Server 2008 R2: Remove single occurrence record. -
i have following table 2 columns shown below:
create table test_lin ( cola int, colb int ); insert test_lin values(1,2); insert test_lin values(1,3); insert test_lin values(1,4); insert test_lin values(1,5); insert test_lin values(1,3); insert test_lin values(2,4); insert test_lin values(2,6); insert test_lin values(2,7); insert test_lin values(2,4); insert test_lin values(2,6);
note: want show records repeated more once. in case (1,3),(2,4),(2,6)
records repeated in table.
i want remove single occurrence records result set. records single occurrence shown below in image.
with cte ( select cola,colb,row_number() over(partition cola,colb order cola) rn test_lin ) select t.* test_lin t inner join cte c on c.cola = t.cola , c.colb = t.colb , c.rn=2
Comments
Post a Comment