Wednesday, April 19, 2017

How To Generate ROW_NUMBER with out Using Row_Number() function

;WITH cte AS
 ( select 2 as num
   union all
   select 3 as num
   union all
   select 2 as num
   union all
   select 3 as num
   union all
   select 4 as num
   union all
   select 5 as num
   union all
   select 2 as num
)
SELECT num
,Row_number() over(partition by num order by num ) as row_num
,sum(1) OVER( PARTITION BY num  ORDER BY num ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as final --- row number replace
,sum(1) OVER( PARTITION BY num  ORDER BY num) as final1--> it will give count

FROM cte


No comments:

Post a Comment