This content has been marked as final.
Show 3 replies

1. Re: row_number within repeated group of same values
Etbin Mar 23, 2011 7:10 AM (in response to UW (Germany))NOT TESTED !
Regardsselect n,t,row_number() over (partition by grp order by n) v from (select n,t,last_value(frst ignore nulls) over (partition by null order by n) grp from (select n,t,case when t != lag(t,1,t) over (order by n) then t end frst from demo ) )
Etbin
Edited by: Etbin on 23.3.2011 13:08
partition by null added 
2. Re: row_number within repeated group of same values
UW (Germany) Mar 23, 2011 6:36 AM (in response to Etbin)For the A's it starts with 1 again, but not for the B's and C's
select n,t,row_number() over (partition by grp order by n) v from (select n,t,last_value(frst ignore nulls) over (order by n) grp from (select n,t,case when t != lag(t,1,t) over (order by n) then t end frst from demo ) ) order by n N T V    1 A 1 2 A 2 3 B 1 4 C 1 5 C 2 6 C 3 7 A 1 8 A 2 9 B 2 10 C 4

3. Re: row_number within repeated group of same values
Frank Kulash Mar 23, 2011 6:53 AM (in response to UW (Germany))Hi,
Here's one way:
Output:WITH got_grp_num AS ( SELECT n, t , ROW_NUMBER () OVER ( ORDER BY n)  ROW_NUMBER () OVER ( PARTITION BY t ORDER BY N ) AS grp_num FROM demo  WHERE ...  If you need any filtering, put it here ) SELECT n, t , ROW_NUMBER () OVER ( PARTITION BY t , grp_num ORDER BY n ) AS v FROM got_grp_num ORDER BY n ;
Etbin had the right approach; you need to PARTITION BY something finer than t; it has to be a subdivision of t, where all consecutive rows with the same t are in the same group. You could get that group number using LAG or LEAD, like Etbin tried, but it typically takes 2 subqueries.` N T V    1 A 1 2 A 2 3 B 1 4 C 1 5 C 2 6 C 3 7 A 1 8 A 2 9 B 1 10 C 1