La crème de la crème (consultas TOP-N en Oracle)

En esta entrada hablaba de cómo seleccionar las N primeras filas de una tabla. En todos los casos (Informix, SQL Server, SQLite, Oracle), hacía una consulta básica y nos quedábamos con los 10 primeros resultados. Las filas en una tabla relacional no están ordenadas, por definición, lo cual significa que la consulta nos da las N primeras filas aleatorias, es decir, sin ordenar.

Pero la cosa cambia un poco si las queremos ordenadas, es decir los mejores resultados, las mejores notas, los mejores salarios, la crème de la crème... (o los peores).

 

Queremos los resultados crème de la crème

Esto significa sacar las N filas primeras (o mínimas) o las N filas últimas (o máximas), lo que se denomina a veces consultas TOP-N (TOP-N queries, por si queréis buscarlo en inglés).

En el caso de SQLite3 podíamos poner la cláusula LIMIT después del ORDER BY, como se explicaba en aquella entrada. Pero en ORACLE no existe la cláusula LIMIT, así que habrá que hacerlo de alguna otra manera.

En principio, utilizando la columna "mágica" ROWNUM, si hacemos lo siguiente:

SELECT * FROM TABLA WHERE ROWNUM <= 100

estamos obteniendo 100 filas aleatorias. Uno podría pensar que entonces, para obtener las TOP-100 filas bastaría con añadir una condición ORDER BY, tal que así:

SELECT * FROM TABLA WHERE ROWNUM <= 100 ORDER BY CAMPO

Pero eso no funcionaría. Lo que nos estaría dando son 100 filas aleatorias, y una vez seleccionadas (y no antes), nos las mostraría ordenadas por el CAMPO indicado. Pero eso no significa que nos esté dando las 100 filas PRIMERAS (según en el orden indicado) de la tabla. El problema es que el valor de ROWNUM se está asignando justo al seleccionar la fila, y la operación ORDER BY se aplica después.

Así pues, la forma correcta de hacerlo en Oracle es intercalar una SELECT dentro de otra:

SELECT * FROM (la-consulta-original) WHERE ROWNUM <= 100

Es decir, para el caso que nos ocupa:

SELECT * FROM (SELECT * FROM TABLA ORDER BY CAMPO) WHERE ROWNUM <= 100

Ale, problema resuelto. A seguir tecleando.

--

Nota: no confundir ROWNUM con ROWID. Ambas son columnas que asigna ORACLE automáticamente, pero no significan lo mismo.

Si queréis una explicación muy buena y bastante detallada acerca de la diferencia entre ROWNUM y ROWID y cómo utilizar ROWNUM para consultas TOP-N y para resultados paginados, echadle un vistazo a este artículo

http://www.epidataconsulting.com/tikiwiki/tiki-read_article.php?articleId=63

SQL - Seleccionar sólo las primeras N filas

El otro día metí la pata haciendo un SELECT genérico de una tabla de la que desconocía el tamaño. El procesador se puso casi al 100% durante 5 minutos, y el disco duro no dejaba de rascar. Los usuarios que compartían (sufrían) conmigo el acceso a la BD estuvieron experimentando un rendimiento pésimo durante esos 5 minutos (bueno, los pocos que intentaron trabajar en ese período), que a mí se me hicieron eternos. El problema es que la tabla tenía casi 2 millones de registros y esto fue er... bueno... "un poco" pesado. Encima, el resultado fue a una página web, así que os podéis imaginar el tamaño del "ascensor" (el recuadro) de la barra de desplazamiento. Casi no se podía pinchar en él de pequeño que se quedó.

El caso es que me interesaba simplemente ojear el contenido de esa tabla, me hubiera bastado con recuperar unos pocos registros, digamos, unos 100 para hacerme una idea de su contenido. Bueno, pues eso se puede hacer sin necesidad de poner filtros a los datos (cosa que en su momento no podía hacer pues no tenía ni idea de los campos y datos de la tabla).

En ORACLE

SELECT * FROM TABLA WHERE ROWNUM <100

En MS SQL Server y en SYBASE
SELECT TOP 10 * FROM TABLA

En INFORMIX
SELECT FIRST 100 * FROM TABLA


Actualización 02/09/2014 para incluir SQLite3, que me ha hecho falta hoy. Hay que añadir LIMIT(N) al final de la consulta

En SQLite3
SELECT * FROM TABLA LIMIT(100)

Si hubiera cláusulas WHERE u ORDER BY, hay que poner la opción LIMIT al final de todo, o sea:

SELECT * FROM TABLA WHERE CONDICION ORDER BY CAMPO LIMIT(100)