Count repetition of rows

use this syntax:-


create table mytable (mytableid int primary key, userid int,counterid int )

insert into mytable values (

1,           1,         4),
(2   ,        1 ,        7),
(3  ,         4  ,       3),
(4,           11,        9),
(5          , 11 ,       3),
(6           ,4   ,      6),
(9           ,11   ,     9)


select * from mytable


main syntax will be:-



;WITH Ordering
AS
(
  SELECT mytableid,       
  userid,       
  counterid,       
 
  ROW_NUMBER() OVER (ORDER BY userid, counterid) RN
  FROM mytable
)
SELECT
  CurrentRow.mytableid,
  CurrentRow.userid,
  CurrentRow.counterid,
 
  CurrentRow.RN -
    ISNULL((SELECT TOP 1 RN FROM Ordering O3 WHERE RN < CurrentRow.RN AND (CurrentRow.mytableid <> O3.mytableid OR CurrentRow.counterid <> O3.counterid OR CurrentRow.userid <> O3.userid) ORDER BY RN DESC), 0) Repetitions
FROM Ordering CurrentRow
LEFT JOIN Ordering O2 ON CurrentRow.RN + 1 = O2.RN
WHERE O2.RN IS NULL OR (CurrentRow.mytableid <> O2.mytableid OR CurrentRow.counterid <> O2.counterid OR CurrentRow.userid <> O2.userid )
ORDER BY CurrentRow.RN