oracle:rank

最新推荐文章于 2024-10-13 20:14:57 发布

liu_g927

最新推荐文章于 2024-10-13 20:14:57 发布

阅读量83

点赞数

分类专栏：随手记文章标签：数据库

本文链接：https://blog.csdn.net/liu_g927/article/details/84239901

版权

随手记专栏收录该内容

9 篇文章 0 订阅

订阅专栏

Term: RANK

Definition:
In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of rows. Its return type is number and serves for both aggregate and analytic purpose in SQL.

Example Syntax:

Used as an Aggregate function:

RANK (expression) WITHIN GROUP (ORDER_BY expression [ASC | DESC] NULLS [FIRST | LAST]  )

Used as an Analytic function:

RANK () OVER (PARTITION BY expression ORDER_BY expression)

Example Usage:

The below SQL query uses demonstrates analytic behavior of RANK function. It ranks the salary of the employees working in the same department.

SELECT DEPT, EMPNO, RANK() OVER 
(PARTITION BY DEPT ORDER_BY SAL) RANK
FROM EMPLOYEE
/

      DEPT      EMPNO       RANK
---------- ---------- ----------
        10        100          1
        10        110          2
        20        140          1
        20        170          2
        20        120          3
        30        130          1
        30        180          2
        40        150          1
        50        160          1

9 rows selected.