## Saturday, 26 January 2008

### RANK, DENSE_RANK AND ROW_NUMBER analytic functions

I wrote this test to better understand the use of RANK, DENSE_RANK and ROW_NUMBER Oracle analytic functions and Top-N querying. The beauty of this exercise is that all 3 analytic functions are illustrated with one example.

Suppose the business requirement in this case is to rank the products of each customer by the quantity purchased.

The test table I used for this is:
`CREATE TABLE t(customer_name VARCHAR2(50),product_name VARCHAR2(50),hits NUMBER)`

The data in the table is 16 rows like below:
`INSERT INTO t VALUES('bill', 'egg', 1);INSERT INTO t VALUES('bill', 'egg', 1);INSERT INTO t VALUES('bill', 'beer', 3);INSERT INTO t VALUES('bill', 'beer', 2);INSERT INTO t VALUES('bill', 'jeans', 1);INSERT INTO t VALUES('larry', 'beer', 4);INSERT INTO t VALUES('larry', 'beer', 2);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'cheese', 1);INSERT INTO t VALUES('larry', 'olives', 1);INSERT INTO t VALUES('larry', 'olives', 1);INSERT INTO t VALUES('larry', 'car', 1);COMMIT;`

`SELECT * FROM t;CUSTOMER_NAME      PRODUCT_NAME            HITS------------------ ----------------- ----------bill               egg                        1bill               egg                        1bill               beer                       3bill               beer                       2bill               jeans                      1larry              beer                       4larry              beer                       2larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              cheese                     1larry              olives                     1larry              olives                     1larry              car                        1`

The solution query

`SELECTcustomer_name,product_name,hits,rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) rank,dense_rank() over(PARTITION BY customer_name ORDER BY hits DESC nulls LAST) denserank,row_number() over(PARTITION BY customer_name ORDER BY hits DESC) rownumberFROM(SELECTcustomer_name,product_name,SUM(hits) hitsFROM tGROUP BY customer_name, product_name);CUSTOMER_NAME   PRODUCT_NAME          HITS       RANK  DENSERANK  ROWNUMBER--------------- --------------- ---------- ---------- ---------- ----------bill            beer                     5          1          1          1bill            jeans                    1          2          2          2bill            egg                      1          2          2          3larry           cheese                   6          1          1          1larry           beer                     6          1          1          2larry           olives                   2          3          2          3larry           car                      1          4          3          47 rows selected.`

Observe how dense rank will not skip the ranking sequence when products with the same amount of hits are ranked with the same rank. In this case Larry's cheese and beer are ranked in rank 1 in the DENSERANK column and Larry's olives immediately after that are ranked with 2. Whereas the situation is quite different for this case in the RANK column where Larry's olives are ranked with 3.