0
     A          B          C
     1        132          1 
     1        155          2 
     1        132          3 
     1        132          4 
     1        155          5
     1        155          6 
     1        132          7

select A, B, [...] as D
from myTable
order by C

Query must be ordered by C and should return:

     A          B          D
     1        132          1 
     1        155          2
     1        132          3 
     1        132          3 
     1        155          4
     1        155          4
     1        132          5 

All A and B pair groups must have unique D value.

Is it possible to calculate some kind of ID for groups based on order ?

Tried with RANK and GROUPING SETS but with not success.

2

Get the data, order by C. Compute a temporary column: if B equals to B in the previous row (rows ordered by C), let that column be 0, otherwise 1. This column is used for the following: when B is the same as B in the previous row, we increase the value in D by 0, otherwise we increase the value by 1. Finally, get A, B, and the SUM of the temporary column up to the current row (this requires the analytic version of SUM), this will give us D.

The above translated to SQL:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual 
)
select * from data order by c;

         A          B          C
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          4
         1        155          5
         1        155          6
         1        132          7

Get the expected result:

with data as
(
  select 1 a, 132 b, 1 c from dual union all
  select 1 a, 155 b, 2 c from dual union all
  select 1 a, 132 b, 3 c from dual union all
  select 1 a, 132 b, 4 c from dual union all
  select 1 a, 155 b, 5 c from dual union all
  select 1 a, 155 b, 6 c from dual union all
  select 1 a, 132 b, 7 c from dual 
)
select
  a, b,
  sum(d) over (order by c rows between unbounded preceding and current row) as d
from
(
select
  a, b, c,
  case when lag(b) over (order by c) = b
    then 0
    else 1
  end as d
from
  data
)
order by c
;

         A          B          D
---------- ---------- ----------
         1        132          1
         1        155          2
         1        132          3
         1        132          3
         1        155          4
         1        155          4
         1        132          5

Not the answer you're looking for? Browse other questions tagged or ask your own question.