[Oracle] RANK()

오라클 RANK()

 

RANK()

   -

 

RANK(expr) WITHIN GROUP (ORDER BY expr DESC|ASC NULLS FIRST|LAST)

RANK() OVER (query_partition_clause order_by_clause)

의 형태

 

RANK calculates the rank of a value in a group of values. The return type is NUMBER.

 

Rows with equal values for the ranking criteria receive the same rank. Oracle Database then adds the number of tied rows to the tied rank to calculate the next rank. Therefore, the ranks may not be consecutive numbers. This function is useful for top-N and bottom-N reporting.

 

• As an aggregate function, RANK calculates the rank of a hypothetical row identified by the arguments of the function with respect to a given sort specificaltin. The arguments of the function must all evaluate to constant expression within each aggregate group, because the identify a single row within each group. The constant argument expressions and the expressions in the ORDER BY clause of the aggregate match by position. Therefore, the number of arguments must be the same and their types must be compatible.
• As an analytic function, RANK computes the rank of each row returned from a query with respect to the other rows returned by the query, based on the values of the value_exprs in the order_by_clause.

 

오라클 oe 계정 또는 hr 계정으로 테스트가 가능.

 

SQL>

SELECT
    RANK(15500, 0.05) WITHIN GROUP (ORDER BY salary, commission_pct) "Rank"
FROM employees ;

   --> 105

 

SQL> SELECT
    RANK(15500) WITHIN GROUP (ORDER BY salary) "Rank"
FROM employees ;

   --> 105

 

SQL>

SELECT
    department_id,
    last_name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK
FROM employees
WHERE department_id =60
ORDER BY RANK, last_name ;
;    

 

 

 

DENSE_RANK 와 조금 다르다

https://web-obj.tistory.com/389

ORACLE DENSE_RANK()