Consultas SQL con datos ordenados y paginación de resultados

Hoy voy a hablar de una cuestión que es aparentemente sencillo de afrontar pero que puede llegar darnos muchos quebraderos de cabeza.

Supongamos que queremos obtener el identificador de departamento y nombre de la tabla de departamentos ordenados por el nombre de manera descendente. La sentencia SQL sería algo así:

SQL> SELECT department_id, department_name 
  2  FROM DEPARTMENTS
  3  ORDER BY department_name DESC; 

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          120 Treasury
           50 Shipping
          150 Shareholder Services
           80 Sales
          250 Retail Sales
          260 Recruiting
           30 Purchasing
           70 Public Relations
          270 Payroll
          200 Operations
          220 NOC

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           20 Marketing
          170 Manufacturing
          210 IT Support
          230 IT Helpdesk
           60 IT
           40 Human Resources
          240 Government Sales
          100 Finance
           90 Executive
          130 Corporate Tax
          140 Control And Credit

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          190 Contracting
          180 Construction
          160 Benefits
           10 Administration
          110 Accounting

27 filas seleccionadas. 

Si queremos obtener sólo las primeras 11 filas según el mismo orden, una primera posibilidad sería algo como esto:

SQL> SELECT department_id, department_name 
  2  FROM DEPARTMENTS
  3  WHERE ROWNUM <= 11
  4  ORDER BY department_name DESC;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           50 Shipping
           80 Sales
           30 Purchasing
           70 Public Relations
           20 Marketing
           60 IT
           40 Human Resources
          100 Finance
           90 Executive
           10 Administration
          110 Accounting

11 filas seleccionadas. 

ROWNUM es una pseudocolumna que contiene un número que indica el orden en que Oracle selecciona la filas de la consulta, empezando por 1. El problema es que el ROWNUM de cada fila se determina antes de hacer la ordenación. Por ese motivo la consulta no funciona bien.


Se nos podría ocurrir solucionarlo mediante la encapsulación la consulta dentro de otra consulta y filtrar por el ROWNUM de la consulta externa:

SQL> SELECT *
  2  FROM (
  3      SELECT department_id, department_name 
  4      FROM DEPARTMENTS
  5      ORDER BY department_name DESC
  6  ) WHERE ROWNUM <= 11;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          120 Treasury
           50 Shipping
          150 Shareholder Services
           80 Sales
          250 Retail Sales
          260 Recruiting
           30 Purchasing
           70 Public Relations
          270 Payroll
          200 Operations
          220 NOC

11 filas seleccionadas. 

Parece que funciona bien, pero existe un problema: ¡si usamos el operador >= con ROWNUM no funcionará! Tampoco si queremos buscar una fila concreta, pero sólo si es superior a la 1ª (¿?). Ejemplos:

SQL> SELECT *
  2  FROM (
  3      SELECT department_id, department_name
  4      FROM DEPARTMENTS
  5      ORDER BY department_name DESC
  6  ) WHERE ROWNUM = 1;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
          120 Treasury

SQL> SELECT *
  2  FROM (
  3      SELECT department_id, department_name 
  4      FROM DEPARTMENTS
  5      ORDER BY department_name DESC
  6  ) WHERE ROWNUM = 2;

ninguna fila seleccionada

SQL> SELECT *
  2  FROM (
  3      SELECT department_id, department_name 
  4      FROM DEPARTMENTS
  5      ORDER BY department_name DESC
  6  ) WHERE ROWNUM >= 2;

ninguna fila seleccionada 

La solución definitiva consiste en volver a encapsular la consulta anterior, renombrando la pseudocolumna ROWNUM interior para poder referenciarla desde fuera:

SQL> SELECT department_id, department_name
  2  FROM (
  3      SELECT department_id, department_name, ROWNUM numfila
  4      FROM (
  5          SELECT *
  6          FROM DEPARTMENTS
  7          ORDER BY department_name DESC
  8      )
  9  ) WHERE numfila = 2;

DEPARTMENT_ID DEPARTMENT_NAME
------------- ------------------------------
           50 Shipping 

Conociendo esto ya podemos realizar una consulta con resultados ordenados de manera que podamos seleccionar un rango de filas a mostrar, por ejemplo:

SQL> SELECT department_id, department_name
  2  FROM (
  3      SELECT department_id, department_name, ROWNUM numfila
  4      FROM (
  5          SELECT *
  6          FROM DEPARTMENTS
  7          ORDER BY department_name DESC
  8      )
  9  ) WHERE numfila BETWEEN 11 AND 20;