You Asked
Hi Tom,
I need help to get sequence no or rank whenever there is change in continuous sequence below is the example-
Input
seq,id,value
1,1,200
2,1,200
3,1,300
4,1,200
5,1,200
6,1,500
7,1,500
8,1,700
expected out put same combination of id,value as per sequence order should be assigned one value.
for id 1 and values 200 --rnk should be 1 then there is a change in 3rd row --then rnk=2 then 4th row should be assigned with new rnk 3 not 1 ans so on.
seq,id,value, rnk
1,1,200,1
2,1,200,1
3,1,300,2
4,1,200,3,
5,1,200,3,
6,1,500,4
7,1,500,4
8,1,700,5
9,1,800,6
I have tried lead, lag, first_value,last_value ,rank,dense_rank and row_number analytical function ,but i could not achieve the requirement.
and Chris said...
So you want to sort the values by SEQ, then assign ranks based when the VALUE changes?
Here's one way to do it using pattern matching (match_recognize)
- Partition by id and sort by seq
- Group together rows where the current value = prev ( value )
- You want this for any row, followed by any number of rows; so that's a pattern of ( init same* )
- Use match_number to assign group numbers
- Use this group number in dense_rank to assign the ranks
Which gives:
create table t (
c1 int, c2 int, c3 int
);
insert into t values ( 1,1,200 );
insert into t values ( 2,1,200 );
insert into t values ( 3,1,300 );
insert into t values ( 4,1,200 );
insert into t values ( 5,1,200 );
insert into t values ( 6,1,500 );
insert into t values ( 7,1,500 );
insert into t values ( 8,1,700 );
commit;
select c1, c2, c3,
dense_rank () over (
partition by c2
order by grp
) rk,
grp
from t
match_recognize (
partition by c2
order by c1
measures
match_number() as grp
all rows per match
pattern ( init same* )
define
same as c3 = prev ( c3 )
);
C1 C2 C3 SEQ GRP
1 1 200 1 1
2 1 200 1 1
3 1 300 2 2
4 1 200 3 3
5 1 200 3 3
6 1 500 4 4
7 1 500 4 4
8 1 700 5 5 Learn more about pattern matching at
https://www.slideshare.net/ChrisSaxon1/how-to-find-patterns-in-your-data-with-sql
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment