[Oracle]ROW_NUMBER()

오라클 ROW_NUMBER()

 

ROW_NUMBER()

   - 

 

ROW_NUMBER() OVER (query_partition_clause order_by_clause)

의 형태

 

ROW_NUMBER is an analytic function. It assigns a unique number to each row to which it is applied (either each row in the partition or each row returned by the query), in the ordered sequence of rows specified in the order_by_clause, beginning with 1.

 

By nesting a subquery using ROW_NUMBER inside a query that retrieves the ROW_NUMBER values for a specified range, you can find a precise subset of rows from the results of the inner query. This use of the function lets you implement top-N, bottom-N, and inner-N reporting. For consistent results, the query must ensure a deterministic sort order.

 

You cannot nets analytic functions by using ROW_NUMBER or any other analytic function for expr. However, you can use other built-in function expression for expr.

 

오라클 oe 계정, hr 계정으로 테스트 가능

 

 

SQL>
SELECT department_id, first_name, last_name, salary, rn
    FROM (  
        SELECT 
                department_id, first_name, last_name, salary,
                ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary desc) rn 
            FROM employees
        )
        WHERE RN <=3
        ORDER BY department_id, salary DESC, last_name
         ;