Top N 쿼리

Top N 쿼리는 정렬된 결과에서 상위 N건만 가져오는 쿼리다. DBMS마다 문법이 다르고, 특히 Oracle ROWNUM은 정렬보다 먼저 부여된다는 점 때문에 함정 문제가 많이 출제된다.

예제 데이터

EMP 테이블 (저장된 순서 그대로 표시)

급여 내림차순(동점은 이름순)으로 정렬하면 최윤 4000, 김민 3000, 정하 3000, 박솔 2000, 이준 2000, 한결 1000 순서다.

Oracle ROWNUM 동작 원리

ROWNUM은 쿼리 결과로 행이 하나씩 추출될 때마다 1부터 차례로 붙는 가상 컬럼(Pseudo Column)이다. 논리적인 처리 순서는 다음과 같다.

즉 ROWNUM은 ORDER BY보다 먼저 부여된다. 번호가 붙은 뒤에 정렬하므로, 정렬하면 번호 순서가 뒤섞일 뿐 "상위 N건"이 되지는 않는다.

함정 1: 정렬 전에 잘린다

-- 잘못된 예: 급여 상위 3명을 기대
SELECT ENAME, SAL
FROM   EMP
WHERE  ROWNUM <= 3
ORDER  BY SAL DESC;

먼저 읽힌 아무 3건(여기서는 저장 순서상 앞의 3건)을 고른 뒤 정렬했으므로 실제 최고 급여자 최윤이 빠졌다.

올바른 방법: 인라인 뷰에서 먼저 정렬

SELECT ENAME, SAL
FROM   (SELECT ENAME, SAL
        FROM   EMP
        ORDER  BY SAL DESC, ENAME)
WHERE  ROWNUM <= 3;

함정 2: ROWNUM > 1, ROWNUM = 2는 결과가 없다

SELECT ENAME FROM EMP WHERE ROWNUM = 1;   -- 1건
SELECT ENAME FROM EMP WHERE ROWNUM <= 2;  -- 2건
SELECT ENAME FROM EMP WHERE ROWNUM = 2;   -- 0건
SELECT ENAME FROM EMP WHERE ROWNUM > 1;   -- 0건

flowchart LR
  R["다음 행 읽기"] --> N["ROWNUM 1 부여<br/>(통과한 행이 아직 없음)"]
  N --> C{"ROWNUM > 1 ?"}
  C -- "거짓: 탈락<br/>번호 소비 안 함" --> R
  C -. "참이 되는 행 없음" .-> X["결과 0건"]

첫 행은 ROWNUM 1을 받는데 ROWNUM > 1 조건에 탈락한다. 탈락한 행은 번호를 소비하지 않으므로 다음 행도 다시 ROWNUM 1을 받고 또 탈락한다. 결국 어떤 행도 조건을 통과하지 못한다.

시험 포인트: ROWNUM은 =1, <, <= 조건에만 의미가 있다. ROWNUM > n이나 ROWNUM = 2처럼 1을 포함하지 않는 조건은 항상 0건이다.

SQL Server TOP

SQL Server는 SELECT 절에 TOP (n)을 써서 출력 행 수를 제한한다. TOP은 ORDER BY로 정렬된 결과에 적용되므로 인라인 뷰가 필요 없다.

SELECT TOP (3) ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC;

WITH TIES

WITH TIES는 마지막 순위와 같은 값을 가진 행을 함께 출력한다. 반드시 ORDER BY와 함께 써야 한다.

SELECT TOP (2) WITH TIES ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC;

2건을 요청했지만 2번째 행과 급여가 같은 정하까지 포함되어 3건이 출력된다.

시험 포인트: TOP WITH TIES는 ORDER BY 없이 쓸 수 없고, 결과 건수가 n보다 많아질 수 있다.

표준 FETCH FIRST와 OFFSET

SQL 표준 문법으로 Oracle 12c 이상, SQL Server 2012 이상에서 지원한다(SQL Server는 OFFSET 절이 필수).

-- Oracle: 상위 3건
SELECT ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC, ENAME
FETCH  FIRST 3 ROWS ONLY;

-- 동점 포함
SELECT ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC
FETCH  FIRST 2 ROWS WITH TIES;   -- 최윤, 김민, 정하 (3건)

-- SQL Server
SELECT ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC, ENAME
OFFSET 0 ROWS FETCH NEXT 3 ROWS ONLY;

ROW_NUMBER를 활용한 Top N

윈도우 함수를 쓰면 DBMS에 관계없이 같은 방식으로 표현할 수 있고, 그룹별 상위 N건도 쉽게 구할 수 있다.

SELECT ENAME, SAL
FROM   (SELECT ENAME, SAL,
               ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME) AS RN
        FROM   EMP)
WHERE  RN <= 3;

자세한 내용은 윈도우 함수를 참고한다.

페이징 예제

한 페이지에 2건씩 보여줄 때 2페이지(정렬 기준 3~4번째 행)를 조회한다. 결과는 정하 3000, 박솔 2000이다.

Oracle ROWNUM 방식

SELECT ENAME, SAL
FROM   (SELECT ROWNUM AS RN, A.*
        FROM   (SELECT ENAME, SAL
                FROM   EMP
                ORDER  BY SAL DESC, ENAME) A
        WHERE  ROWNUM <= 4)          -- 끝 번호
WHERE  RN >= 3;                      -- 시작 번호

바깥에서 RN으로 시작 번호를 거른다. 별칭이 붙은 RN은 이미 값이 확정된 일반 컬럼이므로 >= 조건이 정상 동작한다.

ROW_NUMBER 방식

SELECT ENAME, SAL
FROM   (SELECT ENAME, SAL,
               ROW_NUMBER() OVER (ORDER BY SAL DESC, ENAME) AS RN
        FROM   EMP)
WHERE  RN BETWEEN 3 AND 4;

OFFSET FETCH 방식

SELECT ENAME, SAL
FROM   EMP
ORDER  BY SAL DESC, ENAME
OFFSET 2 ROWS FETCH NEXT 2 ROWS ONLY;

시험 포인트: 페이징에서 정렬 기준에 동점이 있으면 페이지 사이에 행이 중복되거나 누락될 수 있다. EMPNO 같은 고유 컬럼을 정렬 기준에 추가해 순서를 확정한다.

방식별 비교

핵심 요약

• Oracle ROWNUM은 WHERE 처리 중 부여되어 ORDER BY보다 먼저 정해진다.
• 정렬 후 상위 N건은 인라인 뷰에서 ORDER BY 후 바깥에서 ROWNUM을 건다.
• ROWNUM > 1, ROWNUM = 2는 항상 0건이다.
• SQL Server TOP은 ORDER BY 이후 적용되며 WITH TIES는 동점 행을 추가로 포함한다.
• 표준 OFFSET m ROWS FETCH NEXT n ROWS ONLY로 페이징을 간단히 표현한다.
• ROW_NUMBER는 고유 번호, RANK는 동점 포함 Top N에 쓴다.
• 페이징 정렬 기준에는 고유 컬럼을 추가해 순서를 확정한다.