Purpose
DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Example
The following statement selects the department name, employee name, and salary of all employees who work in the RESEARCH or SALES department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK. SELECT dname, ename, sal, DENSE_RANK()
OVER (PARTITION BY dname ORDER BY sal) as drank
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dname IN ('SALES', 'RESEARCH');DNAME ENAME SAL DRANK
-------------- ---------- ---------- ----------
RESEARCH SMITH 800 1
RESEARCH ADAMS 1100 2
RESEARCH JONES 2975 3
RESEARCH FORD 3000 4
RESEARCH SCOTT 3000 4
SALES JAMES 950 1
SALES MARTIN 1250 2
SALES WARD 1250 2
SALES TURNER 1500 3
SALES ALLEN 1600 4
SALES BLAKE 2850 5是一个分析函数,例如可以查看每个部门最高工资前三位的员工,那用此函数与rnak() over(...),当以一个分组区内,相同工资,那么次序会并列。
DENSE_RANK is an analytic function. It computes the rank of each row returned from a query with respect to the other rows, based on the values of the value_exprs in the ORDER_BY_clause. Rows with equal values for the ranking criteria receive the same rank. The ranks are consecutive integers beginning with 1. The largest rank value is the number of unique values returned by the query. Rank values are not skipped in the event of ties. Example
The following statement selects the department name, employee name, and salary of all employees who work in the RESEARCH or SALES department, and then computes a rank for each unique salary in each of the two departments. The salaries that are equal receive the same rank. Compare this example with the example for RANK. SELECT dname, ename, sal, DENSE_RANK()
OVER (PARTITION BY dname ORDER BY sal) as drank
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dname IN ('SALES', 'RESEARCH');DNAME ENAME SAL DRANK
-------------- ---------- ---------- ----------
RESEARCH SMITH 800 1
RESEARCH ADAMS 1100 2
RESEARCH JONES 2975 3
RESEARCH FORD 3000 4
RESEARCH SCOTT 3000 4
SALES JAMES 950 1
SALES MARTIN 1250 2
SALES WARD 1250 2
SALES TURNER 1500 3
SALES ALLEN 1600 4
SALES BLAKE 2850 5是一个分析函数,例如可以查看每个部门最高工资前三位的员工,那用此函数与rnak() over(...),当以一个分组区内,相同工资,那么次序会并列。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货