1 문제의 SQL문장

2 해결 방안

--drop table #temp
create table #temp(
	a varchar(20)
,	i int
,	ranking int
)

insert #temp(a, i) values('a', 1)
insert #temp(a, i) values('a', 2)
insert #temp(a, i) values('a', 3)
insert #temp(a, i) values('b', 10)
insert #temp(a, i) values('c', 5)
insert #temp(a, i) values('b', 7)
insert #temp(a, i) values('e', 8)

select * from #temp order by 1,2
/*
a                    i           ranking
-------------------- ----------- -----------
a                    1           NULL
a                    2           NULL
a                    3           NULL
b                    7           NULL
b                    10          NULL
c                    5           NULL
e                    8           NULL
*/

update a
set a.ranking = b.ranking
from #temp a 
	inner join (
		select
			a
		,	i
		,	rank() over(partition by a order by i) ranking 
		from #temp) b
	on a.a = b.a
	and a.i = b.i

select * from #temp order by 1,2	
/*
a                    i           ranking
-------------------- ----------- -----------
a                    1           1
a                    2           2
a                    3           3
b                    7           1
b                    10          2
c                    5           1
e                    8           1
*/

update a
set ranking = up_ranking
from (
	select 
		a
	,	i
	,	ranking
	,	rank() over(partition by a order by i) up_ranking
	from #temp) a