Geek asks and answers

SQL: replace immutable values ​​with sequential values

I have not sequential but ordered numerical identifiers. I would like to get consistent 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
+3
source share
4 answers

Another approach, without 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 delete the calculations as a result, execute the query table:

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

+3
source

The totality of your values, use the line number to get the desired value, then join the original values:

SELECT t.original, sq.desired
FROM Table t
INNER JOIN(
  SELECT ssq.original, @rownum:=@rownum+1 ‘desired’
  FROM (
    SELECT t.original
    FROM Table t
    GROUP BY t.original
    ORDER BY t.original
  ) ssq,
  (SELECT @rownum:=0) r 
) sq ON sq.original = t.original
+2
source

, DENSE_RANK :

    SELECT t.original, d.derived
      FROM t
INNER JOIN (    SELECT t.original, COUNT(DISTINCT t1.original) AS "derived"
                  FROM t
            INNER JOIN t t1
                       ON t.original >= t1.original
              GROUP BY 1) d
           ON t.original = d.original
  ORDER BY t.original;
+1

:

select 
    original_value, 
    dense_rank() over (order by original_value) as desired_value
0

More articles:


All Articles