Funciones ordenación:ROW_NUMBER, RANK y DENSE_RANK

ROW_NUMBER, RANK y DENSE_RANK son funciones de ordenación dentro de una partición. La función ROW_NUMBER es una función analítica, que asigna un valor único dentro una partición según un orden determiado. El valor parte de 1.

Diferencias entre ROW_NUMBER, RANK y DENSE_RANK sería:

• ROW_NUMBER: asigna numeros contiguos y unicos desde 1 hasta el último según la ordenación y por cada partición
• RANK: Es igual a ROW__NUMBER, pero no asigna numeros unicos cuando en una ordenación hay dos registros que toman la misma posición
• DENSE_RANK: tampoco asigna numeros unicos, pero si asigna numeros contiguos.

Al utilizar ROW_NUMBER, hay que tener cuidado en que el orden sea único, si no fuera así nos numeraría aleatoriamente los registros, que cumplan el mismo orden.

SINTAXIS:

función () OVER
    ([PARTITION BY <expresion_valor_2> [,<expresion_valor_3>, ...]])
    ORDER BY <expresion_valor_1> [ASC|DESC] 
    [NULLS FIRST | NULLS LAST] [,…])

Función: ROW_NUMBER, RANK o DENSE_RANK
Expresion_valor_2, Expresion_valor_3, …: Puede ser una columna u otra función no analítica a obtener
Expresion_valor_1: Puede ser una columna u otra función no analítica a obtener

EJEMPLO:

CREATE TABLE tabla
(clave NUMBER,
 cantidad NUMBER,
 bloque NUMBER);
 
INSERT INTO tabla (clave, cantidad, bloque) VALUES (1, 1, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (2, 1, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (3, 2, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (4, 2, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (5, 2, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (6, 4, 1);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (7, 5, 2);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (9, 7, 2);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (10,8, 2);
INSERT INTO tabla (clave, cantidad, bloque) VALUES (11,9, 2);
commit;

SELECT clave, cantidad, bloque,
       ROW_NUMBER() OVER (order by clave) fn_row_number,
       ROW_NUMBER() OVER (PARTITION BY bloque order by clave) fn_row_number,
       RANK() OVER (PARTITION BY bloque order by cantidad) fn_rank,
       DENSE_RANK() OVER (PARTITION BY bloque order by cantidad) fn_dense_rank
 FROM TABLA
ORDER BY bloque, clave;

    CLAVE   CANTIDAD     BLOQUE FN_ROW_NUMBER FN_ROW_NUMBER    FN_RANK FN_DENSE_RANK
--------- ---------- ---------- ------------- ------------- ---------- -------------
        1          1          1             1             1          1             1
        2          1          1             2             2          1             1
        3          2          1             3             3          3             2
        4          2          1             4             4          3             2
        5          2          1             5             5          3             2
        6          4          1             6             6          6             3
        7          5          2             7             1          1             1
        9          7          2             8             2          2             2
       10          8          2             9             3          3             3
       11          9          2            10             4          4             4

Utilización de la función ROW_NUMBER, para realizar paginaciones.
EJEMPLO:

SELECT  *
  FROM (SELECT clave, cantidad, bloque,
        	      ROW_NUMBER() OVER (order by clave) elemento
               FROM tabla
         ORDER BY elemento, clave)
  WHERE elemento BETWEEN 3 and 8;


   CLAVE   CANTIDAD     BLOQUE   ELEMENTO
-------- ---------- ---------- ----------
       3          2          1          3
       4          2          1          4
       5          2          1          5
       6          4          1          6
       7          5          2          7
       9          7          2          8