gap-and-islands

 

I'm trying to use rank function in sql server with repeating value in the "partition by" clause, but I can't get exactly what I need. This is my query :

select jobID, runStatus, 
rank() over (partition by runStatus order by jobID ) as rank
from table

and I get:

jobID  runStatus  rank
10     s          9
9      s          8
8      s          7
7      s          6
6      s          5
5      s          4
4      f          1
3      s          3
2      s          2
1      s          1

but what I really need is:

jobID  runStatus  rank desire
10     s          6
9      s          5
8      s          4
7      s          3
6      s          2
5      s          1
4      f          1
3      s          3
2      s          2
1      s          1

meaning that the rank will be initialized every change in runStatus column.

Below is the solution:

You need to define the groups. This is called a "gap-and-islands" problem. And one solution is the difference of row numbers. For your data, this looks like:

select jobID, runStatus, 
       row_number() over (partition by runStatus, seqnum - seqnum_rs
                          order by jobID
                         ) as rank
from (select t.*,
             row_number() over (order by jobId) as seqnum,
             row_number() over (partition by runStatus order by jobId) as seqnum_rs
      from t
     ) t;