loading..
   English
03:44

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:

Console
Execute
  1. SELECT price, DENSE_RANK() OVER(ORDER BY price DESC) dense_rnk,
  2. RANK() OVER(ORDER BY price DESC) rnk,
  3. ROW_NUMBER() OVER(ORDER BY price DESC) rn
  4. FROM PC ORDER BY price DESC;

price dense_rnk rnk rn
980,00 1 1 1
970,00 2 2 2
950,00 3 3 3
850,00 4 4 4
850,00 4 4 5
850,00 4 4 6
600,00 5 7 7
600,00 5 7 8
400,00 6 9 9
350,00 7 10 10
350,00 7 10 11
350,00 7 10 12

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

Console
Execute
  1. SELECT MAX(price) "2nd_price" FROM PC
  2. WHERE price < (SELECT MAX(price) FROM PC);

2nd_price
970,00

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
  1. SELECT MAX(price) "3rd_price" FROM PC WHERE price <
  2. (
  3. SELECT MAX(price) FROM PC
  4. WHERE price < (SELECT MAX(price) FROM PC)
  5. );

3rd_price
950,00

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
  1. SELECT DISTINCT price FROM(
  2. SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
  3. ) X WHERE rnk=2;

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
  1. SELECT DISTINCT price FROM(
  2. SELECT DENSE_RANK() OVER(ORDER BY price DESC) rnk, price FROM PC
  3. ) X WHERE rnk=3;

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.

Bookmark and Share
Pages 1 2
Tags
aggregate functions Airport ALL AND AS keyword ASCII AVG Battles Bezhaev Bismarck C.J.Date calculated columns Cartesian product CASE cast CHAR CHARINDEX Chebykin check constraint classes COALESCE common table expressions comparison predicates Computer firm CONSTRAINT CONVERT correlated subqueries COUNT CROSS APPLY CTE data type conversion data types database schema DATEADD DATEDIFF DATENAME DATEPART DATETIME date_time functions DDL DEFAULT DEFAULT VALUES DELETE DISTINCT DML duplicates edge equi-join EXCEPT exercise (-2) More tags
The book was updated
several days ago
https://exchangesumo.com/obmen/RFBRUB-DGC/
©SQL-EX,2008 [Evolution] [Feedback] [About] [Links] [Team]
All right reserved.
Rambler's Top100