Dense_Rank with Data Island


  
 
  
 CREATE TABLE test (StartOfWeek DATETIME,Amount INT)
INSERT  INTO test(StartOfWeek, Amount) VALUES  ('1/17/2016',8),('1/24/2016',8),('1/31/2016',10),('2/7/2016',10),('2/14/2016',14),('2/21/2016',10),('2/28/2016',10)

;with mycte as (
select StartOfWeek, Amount  
, ROW_NUMBER()OVER (ORDER BY StartOfWeek) - ROW_NUMBER()OVER (Partition by Amount ORDER BY StartOfWeek) grp from test)

,mycte2 as (
Select * 
 ,row_number() Over(Order by StartOfWeek) - row_number() Over(Partition by grp Order by StartOfWeek) grp2 FROM mycte
)
SELECT StartOfWeek, Amount , DENSE_RANK() OVER(ORDER BY grp2) AS Ranking  FROM  mycte2
 
 

 drop table test

/*
2016-01-17 00:00:00.000	8	1
2016-01-24 00:00:00.000	8	1
2016-01-31 00:00:00.000	10	2
2016-02-07 00:00:00.000	10	2
2016-02-14 00:00:00.000	14	3
2016-02-21 00:00:00.000	10	4
2016-02-28 00:00:00.000	10	4

*/
-----https://social.msdn.microsoft.com/Forums/en-US/8c24f1a9-f267-4627-969c-5a30d78846a4/row-number?forum=transactsql
 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s