[SQL]ROW_NUMBER() 함수

select row_number() over(partition by 행기준칼럼 order by 정렬기준칼럼)
from 테이블명

 

활용: 가장 최신 버전의 ICD 단어사전을 매핑

-- 진단
-- 수정일: 2021.04.03. / 수정자: 신은재

select 
/*생략*/
     , A.DISS_CD                                as DISS_CD              -- 질병코드(ICD-10th)
     , V1.ICD_10_NM                             as DISS_ENM             -- 질병영문명(ICD-10th)
     , V2.ICD_10_NM                             as DISS_KNM             -- 질병한글명(ICD-10th)
  from 진단테이블                       A  -- 진단관리
/*생략*/
       left outer join
          (
           select ICD_10_CD
                , ICD_10_NM
				, ROW_NUMBER() OVER(PARTITION BY ICD_10 ORDER BY substr(용어사전버전,4,1) DESC) as KCD_NO
             from 용어사전테이블
            where /*생략*/
          )  V1 -- 한글
         on substr(A.DISS_CD,1,decode(instr(A.DISS_CD,'_'),'0',length(A.DISS_CD),instr(A.DISS_CD,'_')-1)) = V1.ICD_10_CD
		 and V1.KCD_NO = 1
       left outer join
          (
           select ICD_10_CD
                , ICD_10_NM
				, ROW_NUMBER() OVER(PARTITION BY ICD_10 ORDER BY substr(용어사전버전,4,1) DESC) as KCD_NO
             from 용어사전테이블
            where  /*생략*/            
          )  V2 -- 영문
		 on substr(A.DISS_CD,1,decode(instr(A.DISS_CD,'_'),'0',length(A.DISS_CD),instr(A.DISS_CD,'_')-1)) = V2.ICD_10_CD
		 and V2.KCD_NO = 1
/*생략*/