Skip to Main Content
  • Questions
  • Session wise rank ,change in value should lead to new rank

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Rakesh.

Asked: July 10, 2020 - 3:22 am UTC

Answered by: Chris Saxon - Last updated: July 13, 2020 - 12:40 pm UTC

Category: SQL - Version: oracle 12c

Viewed 100+ times

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 we 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

and you rated our response

  (2 ratings)

Reviews

Incredible

July 10, 2020 - 11:13 am UTC

Reviewer: Rakesh Gupta from India

Thanks alot ,I never knew match_recognize even exist.
Your solution made my work simple.
Getting time to understand how each piece actually works.
Please do share more features like this.
I went through your SlideShare it's incredible.
Thanks alot.

Another doubt

July 10, 2020 - 11:23 am UTC

Reviewer: Rakesh Gupta from India

Another doubt , how to handle scenario if my id changes and need incremental grp values not resetting it.
seq,id,value,grp
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,2,600,6
10,2,200,7
11,2,400,8
12,3,600,9
13,4,900,10
14,5,500,11
15,5,500,11
16,6,540,12

Chris Saxon

Followup  

July 13, 2020 - 12:40 pm UTC

If you want the group values to increase across the whole data set, remove the partition by clause.

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.