# Rank groups based on order [closed]

``````     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.

## 1 Answer

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