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.

enter image description here

i want remove single occurrence records result set. records single occurrence shown below in image.

enter image description here

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

Popular posts from this blog

html - Outlook 2010 Anchor (url/address/link) -

javascript - Why does running this loop 9 times take 100x longer than running it 8 times? -

Getting gateway time-out Rails app with Nginx + Puma running on Digital Ocean -