DENSE_RANK() Analytic Function

DENSE_RANK-ANALYTIC-FUNCTION

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

If two or more rows tie for a rank, each tied rows receives the same rank. For example, if the two top employees have the same salary value, they are both ranked one. The employee with the next highest salary is ranked number two.

This is one more than the number of distinct rows that come before this row. Therefore, the numbers returned by the DENSE_RANK function do not have gaps and always have consecutive ranks.

The sort order that is used for the whole query determines the order in which the rows appear in a result set.This implies that a row ranked number one does not have to be the first row in the partition.

PARTITION BY clause:

Divides the query result set into partitions. The window function is applied to each partition separately and computation restarts for each partition.

Read also: Difference Between INTERVAL And ADD_MONTHS in Teradata

ORDER BY clause:

Defines the logical order of the rows within each partition of the result set. That is, it specifies the logical order in which the window function calculation is performed.

Example Query:

select ename,deptno,sal,DENSE_RANK() OVER (PARTITION BY deptno ORDER BY SAL) from emp;
dense-rank-function-sql

Here 7th and 8th rows assigned with Rank-4. same for 10 and 11 rows.

So this function always returns consecutive numbers.

If you want apply DENSE_RANK() by department wise, we can use PARTITION BY clause.

PARTION BY works like GROUP BY in aggregates.

You can watch video tutorial of DENSE_RANK function here:

Comments

comments

Leave a Reply