[SQLD,SQLP]SQL활용 - Top N 쿼리

1. Definition

SQL 질의 결과에서 행의 순서를 나타내는 열로, 주로 Oracle 에서 사용되며 결과 집합에서 각행에 임시로 부여되는 일련번호입니다.  테이블이나 집합에서 원하는 만큼의 행만 가져오고 싶을 때 WHERE 절에서의 행의 갯수를 제한하는 목적으로 사용됩니다. 

2. Syntax

SELECT column1, column2...
FROM   table_name
WHERE  Condition
AND    ROWNUM <= n
;

ROWNUM은 행 번호를 나타내며, 'n'보다 작거나 같을 때 사용됩니다. 

한 건의 행만 가져오고 싶을 때는, 아래와 같이 SQL문을 작성할 수 있습니다. 

SELECT  employee_id, first_name, last_name
FROM    employees
WHERE   ROWNUM <=1 ;

OR
------------------------------------------
SELECT  employee_id, first_name, last_name
FROM    employees
WHERE   ROWNUM <2 ;

 

조건에 따른 결과 필터링을 할 경우 : orders 테이블에서 order_status가 'Shipped' 인 데이터 중 처음 10개를 선택

SELECT order_id, customer_name, order_date
FROM orders
WHERE order_status = 'Shipped'
AND ROWNUM <= 10;

주의사항으로 Oracle에서 순위가 높은 N개의 행을 추출하기 위해 Order By절과 WHERE 절의 ROWNUM 조건을 같이 사용하는 경우가 있는데, 이 두 조건으로는 원하는 결과를 얻을 수 없다. Oracle의 경우 데이터가 추출 된 후 데이터에 대한 정렬이 일어나므로 주의가 필요합니다. 

SELECT  ENAME, SAL
FROM    EMP
WHERE   ROWNUM < 4
ORDER BY SAL DESC;

실행결과
ENAME    SAL
-----    ----
ALLEN    1600
WARD     1250
SMITH     800

급여가 상위 3명을 추출할 때, 위와 같이 SQL을 작성할 경우, 급여 순서에 상관없이 무작위로 추출 된 3명에 한해 급여를 내림차순으로 정렬한 결과가 출력됩니다. 

따라서, 아래와 같이 인라인 뷰를 사용하여 집합을 내림차순으로 정렬 후 ROWNUM을 적용시켜 원하는 결과를 얻을 수 있습니다. 

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

실행결과
ENAME    SAL
-----    ----
KING     5000
SCOTT    3000
FOR      3000