울면서개발하기
DB / MSSQL

Oracle 의 MAX() KEEP (DENSE_RANK LAST) 를 SQL Server 에서 쓰는 법

· 2026년 10월 25일 다시 씀

“직무별로 급여가 가장 높은 사람의 이름” 처럼, 그룹의 최댓값 그 자체가 아니라 최댓값을 가진 행의 다른 열이 필요할 때가 있다. Oracle 에서는 KEEP (DENSE_RANK LAST ...) 한 줄이면 되는데, SQL Server 에는 KEEP 절이 없다. SQL Server 2022 기준으로 바꿔 쓰는 방법을 정리했다.

예제 데이터

NAME JOB SALARY
남이사 설계자 700
이부장 설계자 680
송차장 설계자 750
강대리 개발자 80
김대리 개발자 130
정사원 개발자 160
심차장 개발자 150

원하는 결과는 이렇다.

JOB SALARY NAME
개발자 160 정사원
설계자 750 송차장

Oracle: KEEP 절

WITH EMP (NAME, JOB, SALARY) AS (
    SELECT '남이사', '설계자', 700 FROM DUAL UNION ALL
    SELECT '이부장', '설계자', 680 FROM DUAL UNION ALL
    SELECT '송차장', '설계자', 750 FROM DUAL UNION ALL
    SELECT '강대리', '개발자',  80 FROM DUAL UNION ALL
    SELECT '김대리', '개발자', 130 FROM DUAL UNION ALL
    SELECT '정사원', '개발자', 160 FROM DUAL UNION ALL
    SELECT '심차장', '개발자', 150 FROM DUAL
)
SELECT A.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;

KEEP (DENSE_RANK LAST ORDER BY A.SALARY) 는 “SALARY 순으로 마지막 순위에 해당하는 행들만 남겨라” 는 뜻이고, 앞의 MAX(A.NAME) 은 그 행들에 적용된다. 그래서 최고 급여자가 여럿이면 그중 이름이 가장 큰 값이 나온다. 아래 SQL Server 쿼리들도 이 동작에 맞췄다. 최솟값 쪽이 필요하면 DENSE_RANK FIRST 이고, SQL Server 쪽에서는 정렬 방향만 바꾸면 된다.

SQL Server: 예제 데이터

SQL Server 에서는 VALUES 로 예제 데이터를 만드는 편이 짧다. 한글 문자열에는 N 접두사를 붙인다. 데이터베이스 기본 데이터 정렬이 한글을 담지 못하는 코드 페이지면 N 없이 쓴 한글이 ? 로 바뀐다.

WITH EMP AS (
    SELECT NAME, JOB, SALARY
      FROM (VALUES (N'남이사', N'설계자', 700)
                 , (N'이부장', N'설계자', 680)
                 , (N'송차장', N'설계자', 750)
                 , (N'강대리', N'개발자',  80)
                 , (N'김대리', N'개발자', 130)
                 , (N'정사원', N'개발자', 160)
                 , (N'심차장', N'개발자', 150)
           ) AS T (NAME, JOB, SALARY)
)
SELECT * FROM EMP;

아래 예제들은 모두 이 WITH EMP AS (...) 뒤에 붙여서 실행하면 된다.

방법 1. ROW_NUMBER — 가장 무난하다

SELECT X.JOB, X.SALARY, X.NAME
  FROM (
        SELECT A.JOB, A.SALARY, A.NAME
             , ROW_NUMBER() OVER (PARTITION BY A.JOB
                                  ORDER BY A.SALARY DESC, A.NAME DESC) AS RN
          FROM EMP A
       ) X
 WHERE X.RN = 1;
  • ORDER BY 에 A.NAME DESC 를 덧붙여서 동점일 때 Oracle 의 MAX(NAME) 과 같은 사람이 나오게 했다. 이 두 번째 정렬 기준이 없으면 동점자 중 누가 나올지 정해지지 않는다
  • 동점자를 모두 보고 싶으면 ROW_NUMBER 대신 DENSE_RANK() 를 쓰고 ORDER BY A.SALARY DESC 만 둔다
  • (JOB, SALARY DESC) 인덱스가 있으면 정렬 없이 읽을 수 있다

방법 2. FIRST_VALUE — 다른 집계와 함께 쓸 때

SELECT DISTINCT
       A.JOB
     , MAX(A.SALARY)      OVER (PARTITION BY A.JOB) AS SALARY
     , FIRST_VALUE(A.NAME) OVER (PARTITION BY A.JOB
                                 ORDER BY A.SALARY DESC, A.NAME DESC) AS NAME
  FROM EMP A;

FIRST_VALUE 는 SQL Server 2012 부터 있다. 그룹별 합계·건수 같은 다른 값도 같이 뽑을 때 편하다.

LAST_VALUE 를 쓰려면 함정이 있다. ORDER BY 가 있는 창 함수의 기본 범위는 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 라서, 범위를 따로 주지 않으면 그룹의 마지막 행이 아니라 현재 행(과 같은 값의 행)까지만 보고 값을 돌려준다.

-- 틀림: 정렬 값이 겹치지 않으면 행마다 자기 이름이 나온다
LAST_VALUE(A.NAME) OVER (PARTITION BY A.JOB ORDER BY A.SALARY, A.NAME)

-- 맞음: 범위를 그룹 끝까지 연다
LAST_VALUE(A.NAME) OVER (PARTITION BY A.JOB ORDER BY A.SALARY, A.NAME
                         ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

그래서 “마지막” 이 필요해도 정렬을 뒤집어 FIRST_VALUE 를 쓰는 쪽이 실수가 적다.

SQL Server 2022 에서는 WINDOW 절로 같은 창 정의를 이름 붙여 재사용할 수 있다(데이터베이스 호환성 수준 160 이상). FIRST_VALUE/LAST_VALUE 에 IGNORE NULLS 를 쓸 수 있게 된 것도 2022 부터다.

SELECT DISTINCT
       A.JOB
     , MAX(A.SALARY)       OVER W_JOB  AS SALARY
     , FIRST_VALUE(A.NAME) OVER W_RANK AS NAME
  FROM EMP A
WINDOW W_JOB  AS (PARTITION BY A.JOB)
     , W_RANK AS (W_JOB ORDER BY A.SALARY DESC, A.NAME DESC);

방법 3. CROSS APPLY + TOP 1 — 그룹 목록이 따로 있을 때

직무 코드 테이블처럼 그룹 목록이 따로 있고 (JOB, SALARY) 인덱스가 있으면, 그룹마다 한 건씩 찾아오는 방식이 빠를 수 있다.

SELECT J.JOB, X.SALARY, X.NAME
  FROM (SELECT DISTINCT JOB FROM EMP) J      -- 실제로는 직무 코드 테이블
 CROSS APPLY (
        SELECT TOP (1) A.SALARY, A.NAME
          FROM EMP A
         WHERE A.JOB = J.JOB
         ORDER BY A.SALARY DESC, A.NAME DESC
       ) X;

해당 직무에 사람이 없어도 직무를 남기려면 OUTER APPLY 를 쓴다.

방법 4. 문자열 결합 트릭 — GROUP BY 를 그대로 두고 싶을 때

원래 글에서 쓴 방법이다. 정렬 기준 값을 고정 길이 문자열로 만들어 이름 앞에 붙이고, 그 문자열의 MAX 를 구한 뒤 앞부분을 잘라 낸다. 기존 GROUP BY 쿼리에 열 하나만 추가하면 되는 것이 장점이다.

원래 글의 식은 이랬다.

SUBSTRING(MAX(FORMAT(A.SALARY, '0000') + A.NAME), 5, 1000)

이 식에는 문제가 있다.

  • FORMAT(..., '0000') 은 최소 4자리다. 급여가 10000 이상이면 5자리가 되어 자르는 위치가 어긋나고, 자릿수가 다른 값끼리는 문자열 비교 순서도 틀어진다 ('10000' < '9000')
  • 음수는 - 가 붙어 순서가 맞지 않는다
  • FORMAT 은 .NET 을 거치는 함수라 행이 많으면 눈에 띄게 느리다

정수이고 0 이상인 값이라면 넉넉한 자릿수로 왼쪽을 0 으로 채우면 된다.

SELECT A.JOB
     , MAX(A.SALARY) AS SALARY
     , SUBSTRING(MAX(RIGHT(REPLICATE('0', 10) + CAST(A.SALARY AS VARCHAR(10)), 10) + A.NAME), 11, 100) AS NAME
  FROM EMP A
 GROUP BY A.JOB;

같은 급여끼리는 붙인 이름까지 비교되므로 동점이면 이름이 큰 쪽이 나와 Oracle 결과와 같다. 다만 날짜·소수·음수가 섞이면 고정 길이 문자열로 만드는 식이 복잡해지고 틀리기 쉽다. 새로 짜는 쿼리라면 방법 1이나 2를 권한다.

정리

상황 추천
그룹별 한 행만 필요 ROW_NUMBER() ... = 1
다른 집계값과 같이 필요 FIRST_VALUE (+ DISTINCT 또는 바깥 GROUP BY)
그룹 목록 테이블 + 인덱스가 있음 CROSS APPLY (SELECT TOP (1) ...)
기존 GROUP BY 를 고치기 어려움 고정 길이 문자열 결합 트릭 (0 이상 정수에만)

어느 방법이든 동점자 처리를 위해 정렬 기준을 하나 더 두는 것을 잊지 않는다.

다시 쓰며 바로잡은 점

  • 원래 글의 FORMAT(A.SALARY, '0000') 트릭은 4자리를 넘는 값·음수에서 틀린 결과가 나오고 느리다. 고정 길이로 자르는 식으로 고치고, 창 함수(ROW_NUMBER, FIRST_VALUE)와 CROSS APPLY 방법을 추가했다
  • Oracle KEEP 의 동점 처리(동점이면 MAX(NAME))를 SQL Server 쿼리에서도 같게 맞췄다
  • SQL Server 예제 문자열에 N 접두사를 붙였다