[MySql]5.x 버전에서 순위(rank ) 구하기

MySql 8.x 버전에서는 윈도우 함수(Window function)를 지원한다. 이 경우 rank(), dense_rank() 함수를 이용하면 쉽게 구할 수 있다.

반면 그 이전 버전 5.x 에서는 아래와 같이 구할 수 있다.

-- rank 
select id
     , name
     , age
     , gender
     , case when @part = gender then @rn := @rn + 1
            else @rn := 1
       end as rn
     , @part := gender as part
  from (select 1 id, '홍길동' as name, 10 age, 'M' gender union all
        select 2 id, '성춘향' as name, 15 age, 'F' gender union all
        select 3 id, '이몽룡' as name, 30 age, 'M' gender union all
        select 4 id, '변사또' as name, 7 age, 'M' gender union all
        select 5 id, '콩쥐' as name, 43 age, 'F' gender union all
        select 6 id, '방자' as name, 32 age, 'M' gender union all
        select 7 id, '향단이' as name, 26 age, 'F' gender) as tmp
     , (select @rn := 0, @part := null) as x
 order by gender asc, age asc
;
/*
id	name	age	gender	rn	part
2	성춘향	15	F	1	F
7	향단이	26	F	2	F
5	콩쥐	43	F	3	F
4	변사또	7	M	1	M
1	홍길동	10	M	2	M
3	이몽룡	30	M	3	M
6	방자	32	M	4	M
*/


-- dense_rank
select id
     , name
     , age
     , gender
     , case when @part = gender and age = @pre_age then @rn
            when @part = gender then @rn := @rn + 1
            else @rn := 1
    end as rk
     , @part := gender as part
     , @pre_age := age as pre_age
  from (select 1 id, '홍길동' as name, 10 age, 'M' gender union all
        select 2 id, '성춘향' as name, 15 age, 'F' gender union all
        select 3 id, '이몽룡' as name, 30 age, 'M' gender union all
        select 4 id, '변사또' as name, 7 age, 'M' gender union all
        select 5 id, '콩쥐' as name, 26 age, 'F' gender union all
        select 6 id, '방자' as name, 32 age, 'M' gender union all
        select 7 id, '향단이' as name, 26 age, 'F' gender union all
        select 8 id, '심청이' as name, 32 age, 'F' gender) as tmp
     , (select @rn := 0, @part := null, @pre_age := null) as x
 order by gender asc, age asc
;

/*
id	name	age	gender	rk	part	pre_age
2	성춘향	15	F	1	F	15
5	콩쥐	26	F	2	F	26
7	향단이	26	F	2	F	26
8	심청이	32	F	3	F	32
4	변사또	7	M	1	M	7
1	홍길동	10	M	2	M	10
3	이몽룡	30	M	3	M	30
6	방자	32	M	4	M	32
*/