¿Hay alguna manera de hacer que una consulta Oracle
se comporte como si contuviera una cláusula MySQL limit
?
En MySQL
, puedo hacer esto:
select *
from sometable
order by name
limit 20,10
para obtener las filas 21 a 30 (omitir las primeras 20, dar las siguientes 10). Las filas se seleccionan después de la orden por
, por lo que realmente comienza en el 20º nombre por orden alfabético.
En Oracle
, lo único que se menciona es la pseudo-columna rownum
, pero se evalúa antes de order by
, lo que significa esto:
select *
from sometable
where rownum <= 10
order by name
devolverá un conjunto aleatorio de diez filas ordenadas por nombre, que no suele ser lo que quiero. Tampoco permite especificar un desplazamiento.
Para ello puede utilizar una subconsulta como
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
También puedes consultar el tema Sobre ROWNUM y la limitación de resultados en Oracle/AskTom para más información.
Actualización: Para limitar el resultado con límites inferiores y superiores las cosas se hinchan un poco más con
select * from
( select a.*, ROWNUM rnum from
( <your_query_goes_here, with order by> ) a
where ROWNUM <= :MAX_ROW_TO_FETCH )
where rnum >= :MIN_ROW_TO_FETCH;
(Copiado de un artículo específico de AskTom)
Actualización 2: A partir de Oracle 12c (12.1) hay una sintaxis disponible para limitar las filas o comenzar en offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Ver esta respuesta para más ejemplos. Gracias a Krumia por la pista.
Una solución analítica con una sola consulta anidada:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
podría sustituirse por Row_Number()
pero podría devolver más registros de los que espera si hay valores duplicados para el nombre.
(no probado) algo como esto puede hacer el trabajo
WITH
base AS
(
select * -- get the table
from sometable
order by name -- in the desired order
),
twenty AS
(
select * -- get the first 30 rows
from base
where rownum < 30
order by name -- in the desired order
)
select * -- then get rows 21 .. 30
from twenty
where rownum > 20
order by name -- in the desired order
También existe la función analítica rank, que puedes utilizar para ordenar por.