RANK() and DENSE_RANK() functions page 2
Example of usage of DENSE_RANK
An often encountered problem is a finding of Nth value in magnitude among a set of values of some column of a table, for example:
Find out the second-largest value of the price in PC table.
Let's deduce the sorted list of values of the price in PC table with additional columns of ranking functions for the control:
In the frame of SQL-92 Standard, we can solve this task as follows:
I.e. we find the maximum among the values which less than maximal value. But what shall we do, if it is needed to find the third-largest value of a price? It is possible to act in the similar manner:
To find out N-largest price value, following the same logic, we could add new "Nested dolls" to already existing ones up to N-th. This decision in any way you will not name universal.
For solving such problems, use of DENSE_RANK window function is a good approach. For example, the initial task can be solved as follows:
To deduce any other price in a sequence (third one, for example), it is enough to replace a constant in the search condition:
It should be noted that use of DENSE_RANK instead of RANK is caused by the case of duplicate price values, which force the RANK function to return the values with gaps (consider the problem of finding out the 5th-largest value). If the problem is stated as finding out the Nth value, which is not obligatory unique, we can use ROW_NUMBER function instead. But if the values are unique in a table, the solution with aid of any of these functions gives the same result.