sql - numbering partitions based on changing dimensions -

i'm having trouble creating right partitions within data. here's example of data looks like, complete desired output:

customer   contract   type1   type2   partition 100        1                      1 100        2                      1 100        3                b       2 100        4                b       2 100        5                b       2 100        6                      3 100        7                      3 100        8          c             4 100        9          c             4 

the variable i'm trying construct last one, called partition. problem have now, when using dense_rank, contracts 1 , 2 grouped contracts 6 , 7:

select   t1.* , dense_rank() on (order customer, type1, type2) partition table1 t1 

what can use generate desired output (on large dataset)?

if understand correctly, want adjacent groups of rows, "adjacent" based on contract.

you can using difference of row_number() values. difference constant, when values adjacent. result provide additional grouping column provides information need:

select t1.*,        dense_rank() on (order customer, type1, type2, grp) partition (select t1.*,              (row_number() on (partition customer order contract) -               row_number() on (partition customer, type1, type2 order contract)              ) grp       table1 t1      ) t1;