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

using table twice in select stmt

i've been asked a question like this:-

table structure is:

mytableid int pk
userid int
counterid int


in this table he has some data like this:

MyTableID   UserID   Counter
---------   -------  --------
1           1         4
2           1         7
3           4         3
4           11        9
5           11        3
6           4         6
...
9           11        9


now his query is, he wants use a select stmt which gives the result with max counterid and the userid should not repeat.......

so many peoples are posted so many syntaxes.........but he is not satisfied with their syntaxes.....

then...i gave the solution like this

select distinct(a.userid),
MAX(a.counterid) as counterid
 from mytable a, mytable b
where a.mytableid=b.mytableid group by a.userid





ff
f
finally it works............


these all are done in famous site WWW.STACKOVERFLOW.COM

shortkuts

shortkut for opening sql server ....type SSMS on run command...it is only applicable in server 2008

CTRL+U for changing of database option

ALT+N  for switch over to new query window

CTRL+R  for minimizing result window



njoy sql programming.....i hope these shortkuts will be useful for many of u......