DataBase/Oracle

[Oracle] 오라클 순번 매기기 함수(ROWNUM, ROW_NUMBER)

mingmongs 2025. 5. 29. 13:37

title

오라클 시스템 함수인 ROWNUM, ROW_NUMBER를 활용하여
행에 순번을 매길 수 있다.
이 함수들에 대하여 알아보자.

 

1) ROWNUM?

: SELECT 해온 데이터에 대해 일련번호를 붙이는 것이다.
주로 테이블에서 원하는 만큼의 행만 가져오고 싶을 때,

행의 개수를 제한하는 용도로 사용한다.
ex) 페이징처리

 

2) ROWNUM 사용법

ROWNUM의 기본 사용법을 아래 EMP_TABLE 테이블을 예시로 설명해보겠다.

img1

※ 참고: 위 테이블은 아래 쿼리로 임시 테이블을 구성한 것이다.

- EMP_NO: 사원번호, EMP_NM: 사원명, HIRE_DATE: 입사일
WITH EMP_TABLE AS (
    SELECT '5020' AS EMP_NO, 'SCOOT' AS EMP_NM, '20120302' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5004' AS EMP_NO, 'BLAKE' AS EMP_NM, '20100821' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5001' AS EMP_NO, 'ANDREW' AS EMP_NM, '20100103' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5006' AS EMP_NO, 'ADAM' AS EMP_NM, '20100901' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5038' AS EMP_NO, 'VICTORIA' AS EMP_NM, '20150617' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5039' AS EMP_NO, 'MARY' AS EMP_NM, '20150617' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5007' AS EMP_NO, 'JAMES' AS EMP_NM, '20100911' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5024' AS EMP_NO, 'NICOLE' AS EMP_NM, '20120302' AS HIRE_DATE FROM DUAL
    UNION ALL
    SELECT '5003' AS EMP_NO, 'SMITH' AS EMP_NM, '20100810' AS HIRE_DATE FROM DUAL
)

 

SELECT *
FROM EMP_TABLE
WHERE ROWNUM <= 4;
;

 

위 쿼리의 의미는 EMP_TABLE에서 1부터 4까지의 ROWNUM에 해당하는 행만 출력한다는 뜻이다.
그 결과는 아래와 같다.

img2

 

이 때, 주의해야할 점이 있다.
위의 예시처럼 ROWNUM을 WHERE 조건에 바로 사용하게 될 경우,

ROWNUM 조건은 무조건 1부터 시작해야 한다.
아래 예시를 살펴보자.

SELECT *
FROM EMP_TABLE
WHERE ROWNUM BETWEEN 2 AND 4;

 

이 경우 결과값은 아무것도 출력되지 않을 것이다.
이유는 ROWNUM은 결과 집합을 만들 때 만들어지기 때문이다.
ROWNUM을 위 경우처럼 특정 순번만을 추출하고 싶은 경우,

서브쿼리를 사용해아 한다.

ROWNUM_TABLE AS (
    SELECT ROWNUM AS NUM, A.*
    FROM EMP_TABLE A
)
SELECT *
FROM ROWNUM_TABLE
WHERE NUM BETWEEN 2 AND 4;

 

위 쿼리는 서브쿼리를 사용하여 2~4번째 행을 가져오는 것이고,

해당 쿼리의 결과값은 아래와 같다.

img3

 

3) ORDER BY와 함께 사용하는 ROWNUM 함수

: 위에서 설명한 ROWNUM의 기본적인 사용법을 ORDER BY와 함께 사용하게 되면

더 유용하게 활용할 수 있다.

DATA_TABLE AS (
    SELECT *
    FROM EMP_TABLE A
    ORDER BY HIRE_DATE ASC
)
SELECT *
FROM DATA_TABLE
WHERE ROWNUM = 1;

 

위 쿼리는 EMP_TABLE을 HIRE_DATE(입사일)을 기준으로 정렬한 뒤,

ROWNUM = 1인 데이터를 추출했으므로 입사일이 가장 빠른 한 명의 사원 데이터를

추출할 수 있다.
그 결과값은 아래와 같다.

img4

 

4) ROW_NUMBER()?

: ROWNUM에서도 ORDER BY한 결과에 대하여 데이터를 추출할 수 있었지만,
ORDER BY된 결과에 순번을 매기는 경우는 ROWNUM보다

ROW_NUMBER 함수가 더 효율적이다.

ROW_NUMBER() 함수를 사용하면

서브쿼리를 사용하지않고도 그룹 순번을 반환할 수 있다.

  • 문법
ROW_NUMBER() OVER(PARTITION BY [그룹핑할 컬럼] ORDER BY [정렬할 컬럼])

   -> PARTITION BY는 선택, ORDER BY는 필수

 

5) ROW_NUMBER() ORDER BY

: 정렬하여 순번을 출력하는 ROW_NUMBER() 기본 예시는 아래와 같다.

SELECT ROW_NUMBER() OVER(ORDER BY HIRE_DATE) NUM, A.*
FROM EMP_TABLE A;

 

해당 쿼리의 결과값은 아래와 같다.
앞서 설명한 ROWNUM처럼 서브쿼리를 사용하지 않아도

정렬과 동시에 순번을 출력할 수 있다.

img5

 

6) ROW_NUMBER() PARTITION BY, ORDER BY

: ROW_NUMBER() 함수에는 그룹별(PARTITION)로 순번을 따로 부여할 수 있다.
아래 테이블을 활용하여 예시를 들어보겠다.

img6

 

아래 예시는 DATA_TABLE을 NAME 그룹 별로 순번을 부여하는 쿼리로,
이 때 정렬 조건은 SCORE DESC(내림차순), NAME ASC(오름차순) 이다.

WITH DATA_TABLE AS (
    SELECT 'A' AS NAME, 1 AS SCORE FROM DUAL
    UNION ALL
    SELECT 'A' AS NAME, 2 AS SCORE FROM DUAL
    UNION ALL
    SELECT 'B' AS NAME, 5 AS SCORE FROM DUAL
    UNION ALL
    SELECT 'C' AS NAME, 3 AS SCORE FROM DUAL
    UNION ALL
    SELECT 'C' AS NAME, 7 AS SCORE FROM DUAL
    UNION ALL
    SELECT 'C' AS NAME, 2 AS SCORE FROM DUAL
),
ROWNUM_TABLE AS(
    SELECT ROW_NUMBER() OVER(PARTITION BY NAME ORDER BY SCORE DESC, NAME ASC) AS NUM
           , A.*
    FROM DATA_TABLE A
)
SELECT *
FROM ROWNUM_TABLE
WHERE NUM = 1;

 

결과값은 아래와 같고, 각 그룹별로 NUM = 1인 행을 출력한 것이다.

img7

 

 

[reference]
https://myblog1128.tistory.com/111
https://coding-factory.tistory.com/443
https://gent.tistory.com/170