What are the differences between Oracle's RANK() and DENSE_RANK() functions, and how do they handle null values?

Deeply explore Oracle’s RANK() and DENSE_RANK() functions

In the field of data analysis and sorting, Oracle provides two distinct functions: RANK() and DENSE_RANK(), each of which has specific uses. This article will explore the subtle differences between these two functions and their application scenarios.

Comparison of RANK() and DENSE_RANK()

The

RANK() function assigns ranks to rows according to a specified order, while the DENSE_RANK() function assigns consecutive ranks. For example, consider the following scenario:

SELECT empname, sal, RANK() OVER (ORDER BY sal) AS r, DENSE_RANK() OVER (ORDER BY sal) AS dr
FROM emptbl;

Empname	Sal	r	dr
rrr	10000	2	2
nnn	20000	3	3
mmm	5000	1	1
kkk	30000	4	4
fff	40000	5	5
ddd	40000	5	5
bbb	50000	7	6
ccc	50000	7	6

In this example, although rrr and nnn have the same salary, the RANK() function assigns them the same rank (2), while the DENSE_RANK() function assigns consecutive ranks, ensuring that no rank is skipped .

Find the Nth salary

To find the nth salary in a table (for example, the third highest salary), you can use the following query:

SELECT sal FROM emptbl ORDER BY sal DESC LIMIT n-1, 1;

Null value handling

The behavior of the RANK() and DENSE_RANK() functions when encountering null values ​​depends on whether NULLS FIRST or NULLS LAST is specified in the ORDER BY clause. For example, consider the following table containing null values ​​for salary:

SELECT empname, sal, RANK() OVER (PARTITION BY deptno ORDER BY sal NULLS FIRST) AS r, DENSE_RANK() OVER (PARTITION BY deptno ORDER BY sal NULLS FIRST) AS dr1
FROM emptbl;

Empname	Sal	r	dr1
fff	40000	1	1
ddd	40000	1	1
rrr	10000	3	2
xxx	NULL	2	3

In this case, the null value is considered to have the lowest rank (NULLS FIRST), resulting in a different rank compared to the case where the null value has the highest rank (NULLS LAST).

Conclusion

Oracle's RANK() and DENSE_RANK() functions provide two different data sorting methods. Understanding their differences and appropriate usage enables you to perform data analysis and manipulation tasks effectively.

The above is the detailed content of What are the differences between Oracle's RANK() and DENSE_RANK() functions, and how do they handle null values?. For more information, please follow other related articles on the PHP Chinese website!