[MSSQL] MAX KEEP

아래와 같은 데이터가 있을 때,

각 JOB별로 가장 많은 SALARY의 NAME을 알고 싶다면 

 

설계자 ☞ 송차장 750

개발자 ☞ 정사원 160

 

ORACLE은 아래와 같이..

WITH EMP AS (
    SELECT '남이사'     AS NAME
         , '설계자'     AS JOB
         , 700          AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '이부장'     AS NAME
         , '설계자'     AS JOB
         , 680          AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '송차장'     AS NAME
         , '설계자'     AS JOB
         , 750          AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '강대리'     AS NAME
         , '개발자'     AS JOB
         , 80           AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '김대리'     AS NAME
         , '개발자'     AS JOB
         , 130          AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '정사원'     AS NAME
         , '개발자'     AS JOB
         , 160          AS SALARY
      FROM DUAL
     UNION ALL  
    SELECT '심차장'     AS NAME
         , '개발자'     AS JOB
         , 150          AS SALARY
      FROM DUAL
)
SELECT A.JOB                                                AS JOB
     , MAX(A.SALARY)                                        AS SALARY
     , MAX(A.NAME) KEEP (DENSE_RANK LAST ORDER BY A.SALARY) AS NAME
  FROM EMP A
 GROUP BY A.JOB

 

MSSQL은 어떻게 하면 좋을까??

WITH EMP AS (
    SELECT '남이사'     AS NAME
         , '설계자'     AS JOB
         , 700          AS SALARY
     UNION ALL  
    SELECT '이부장'     AS NAME
         , '설계자'     AS JOB
         , 680          AS SALARY
     UNION ALL  
    SELECT '송차장'     AS NAME
         , '설계자'     AS JOB
         , 750          AS SALARY
     UNION ALL  
    SELECT '강대리'     AS NAME
         , '개발자'     AS JOB
         , 80           AS SALARY
     UNION ALL  
    SELECT '김대리'     AS NAME
         , '개발자'     AS JOB
         , 130          AS SALARY
     UNION ALL  
    SELECT '정사원'     AS NAME
         , '개발자'     AS JOB
         , 160          AS SALARY
     UNION ALL  
    SELECT '심차장'     AS NAME
         , '개발자'     AS JOB
         , 150          AS SALARY
)
SELECT A.JOB                                                      AS JOB
     , MAX(A.SALARY)                                              AS SALARY
     , SUBSTRING(MAX(FORMAT(A.SALARY, '0000') + A.NAME), 5, 1000) AS NAME
  FROM EMP A
 GROUP BY A.JOB