SQL: replace non-consecutive values with consecutive values

Question

I have non-consecutive, but ordered numeric identifiers. I would like to get consecutive values.

Current Table:

original_value

1
1
1
3
3
29
29
29
29
1203
1203
5230304
5230304
5230304
5230304

Desired Table:

original_value   desired_value

1                1
1                1
1                1
3                2
3                2
29               3
29               3
29               3
29               3
1203             4
1203             4
5230304          5
5230304          5
5230304          5
5230304          5

Answer 1

Another approach, sans joining:

select

  original_value,
  case when @original = original_value then
    @group_number
  else
    @group_number := @group_number + 1
  end,
  (@original := original_value) as x

from tbl,(select @original := null, @group_number := 0) z
order by original_value

Live test: http://www.sqlfiddle.com/#!2/b82d6/6

If you want to remove calculations in result, table-derive the query:

select w.original_value, w.group_number
from
(
  select

    original_value,
    case when @original = original_value then
      @group_number
    else
      @group_number := @group_number + 1      
    end as group_number,
    (@original := original_value) as x

  from tbl,(select @original := null, @group_number := 0) z
  order by original_value
) w

Live test: http://www.sqlfiddle.com/#!2/b82d6/4