New sql features

Ranking functions

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:

Console

Execute

SELECT  price, DENSE_RANK() OVER(ORDER BY price DESC) dense_rnk, 
RANK() OVER(ORDER BY price DESC) rnk,
ROW_NUMBER() OVER(ORDER BY price DESC) rn
FROM PC ORDER BY price DESC;

SELECT  price, DENSE_RANK() OVER(ORDER BY price DESC) dense_rnk, RANK() OVER(ORDER BY price DESC) rnk, ROW_NUMBER() OVER(ORDER BY price DESC) rn FROM PC ORDER BY price DESC;

    Scheme

In the frame of SQL-92 Standard, we can solve this task as follows:

Console

Execute

SELECT MAX(price) "2nd_price"  FROM PC 
WHERE price < (SELECT MAX(price) FROM PC);

SELECT MAX(price) "2nd_price"  FROM PC WHERE price < (SELECT MAX(price) FROM PC);

    Scheme

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:

Console

Execute

SELECT MAX(price) "3rd_price" FROM PC WHERE price <
(
SELECT MAX(price) FROM PC 
WHERE price < (SELECT MAX(price) FROM PC)
);

SELECT MAX(price) "3rd_price" FROM PC WHERE price < ( SELECT MAX(price) FROM PC WHERE price < (SELECT MAX(price) FROM PC) );

    Scheme

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:

Console

Execute

SELECT DISTINCT price FROM(
SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
) X WHERE rnk=2;

SELECT DISTINCT price FROM( SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC ) X WHERE rnk=2;

    Scheme

To deduce any other price in a sequence (third one, for example), it is enough to replace a constant in the search condition:

Console

Execute

SELECT DISTINCT price FROM(
SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
) X WHERE rnk=3;

SELECT DISTINCT price FROM( SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC ) X WHERE rnk=3;

    Scheme

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.