Una consulta Top-N es utilizada para devolver la primera o la última fila en una consulta con ordenamiento.
Oracle 12c introduce la clausula row limiting para simplificar las consultas Top-N y la paginación a través de conjuntos de resultados ordenados.
Ejemplo:
--Crear y llenar una tabla de prueba DROP TABLE prueba_ordenamiento_rownum; CREATE TABLE prueba_ordenamiento_rownum ( valor NUMBER ); INSERT ALL INTO prueba_ordenamiento_rownum INTO prueba_ordenamiento_rownum SELECT level FROM dual CONNECT BY level <= 10; COMMIT;
El siguiente query muestra 20 registros con 10 valores distintos:
SELECT valor FROM prueba_ordenamiento_rownum ORDER BY val; VALOR ---------- 1 1 2 2 3 3 4 4 5 5 6 VALOR ---------- 6 7 7 8 8 9 9 10 10 20 rows selected. SQL>
Consultas Top-N
Sintaxis:
[ OFFSET offset { ROW | ROWS } ]
[ FETCH { FIRST | NEXT } [ { rowcount | percent PERCENT } ]
{ ROW | ROWS } { ONLY | WITH TIES } ]
Ejemplo retornar los 5 máximos valores:
SELECT valor FROM prueba_ordenamiento_rownum ORDER BY valor DESC FETCH FIRST 5 ROWS ONLY; VAL ---------- 10 10 9 9 8 5 rows selected. SQL>
Usando la clausula WITH TIES el resultado puede devolver mas registros si existen múltiples valores en iguales en el resultado de la consulta que compartan el mismo valor que alguno de los N registros devueltos. En este caso la 5 fila tiene un valor de «8», al existir 2 registros con valor «8» ambos son devueltos por la consulta.
SELECT valor FROM prueba_ordenamiento_rownum ORDER BY valor DESC FETCH FIRST 5 ROWS WITH TIES; VALOR ---------- 10 10 9 9 8 8 6 rows selected. SQL>
Adicionalmente para limitar por cantidad de filas la clausula row limiting permite limitar por porcentaje de filas. La siguiente consulta devuelve el 20% inferior de las filas.
SELECT valor FROM prueba_ordenamiento_rownum ORDER BY valor FETCH FIRST 20 PERCENT ROWS ONLY; VALOR ---------- 1 1 2 2 4 rows selected. SQL>
Paginación a través de datos
Hacer una búsqueda a través de un conjunto de resultados ordenado es un poco complejo con el método clásico de consulta Top-N, ya que requiere dos consultas Top-N, una anidada dentro de la otra. Por ejemplo, si se quiere el segundo bloque de 4 filas, se podría hacer lo siguiente
SELECT valor FROM (SELECT valor, rownum AS rnum FROM (SELECT valor FROM prueba_ordenamiento_rownum ORDER BY valor) WHERE rownum <= 8) WHERE rnum >= 5; VALOR ---------- 3 3 4 4 4 rows selected. SQL>
Con la clausula row limiting lo anterior se puede lograr con el siguiente query:
SELECT valor FROM PRUEBA_ORDENAMIENTO_ROWNUM ORDER BY valor OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY; VALOR ---------- 3 3 4 4 4 rows selected. SQL>
El punto de partida para el FETCH es OFFSET + 1.
El OFFSET siempre se basa en un número de filas, pero esto puede combinarse con un FETCH utilizando un PERCENT.
SELECT valor FROM prueba_ordenamiento_rownum ORDER BY valor OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY; VALOR ---------- 3 3 4 4 4 rows selected. SQL>
Información extra
• Las palabras clave ROW y ROWS se pueden usar indistintamente, al igual que las palabras FIRST y NEXT.
• Si el OFFSET no se especifica, se supone que es 0.
• Los valores negativos para el OFFSET, ROW COUNT o PERCENT se tratan como 0.
• Los valores nulos para el OFFSET, ROW COUNT o PERCENT hacen que no se devuelvan filas.
• Las partes fraccionarias de OFFSET, ROW COUNT o PERCENT se truncan.
• Si el OFFSET es mayor o igual que el número total de filas en el conjunto, no se devuelven filas.
• Si el ROW COUNT o el PERCENT son mayores que el número total de filas después del OFFSET, se devuelven todas las filas.
• La cláusula ROW LIMITING no se puede usar con las cláusulas FOR UPDATE, CURRVAL y NEXTVAL de secuencias o en una vista materializada de actualización rápida.
