その３の続き

ていうかナンバリング番号を格納したnum列をもう一回Over句でリナンバリングし直すということもSQL Server2005ならば可能だと、さっきふと思った。

with test_Table as(
　select num = 2 ,code = 2121,Period =200503 union all
　select num = 3 ,code = 2121,Period =200603 union all
　select num = 4 ,code = 2121,Period =200703 union all
　select num = 3 ,code = 7203,Period =200303 union all
　select num = 4 ,code = 7203,Period =200403 union all
　select num = 5 ,code = 7203,Period =200503 union all
　select num = 6 ,code = 7203,Period =200603 union all
　select num = 7 ,code = 7203,Period =200703
)
select code
, period
, num
, num1 = rank() over(partition by code order by num)
, num2 = rank() over(order by code,num)
from test_Table
order by code,num

(続きを読む…)