Is er een manier om een Oracle
query zich te laten gedragen alsof deze een MySQL limit
clausule bevat?
In MySQL
, kan ik dit doen:
select *
from sometable
order by name
limit 20,10
om de 21e tot en met de 30e rij te krijgen (sla de eerste 20 over, geef de volgende 10). De rijen worden geselecteerd na de order by
, dus het begint echt op de 20e naam alfabetisch.
In Oracle
is het enige dat men noemt de rownum
pseudo-column, maar die wordt geëvalueerd vóór de order by
, wat het volgende betekent:
select *
from sometable
where rownum <= 10
order by name
een willekeurige set van tien rijen zal teruggeven, geordend op naam, wat meestal niet is wat ik wil. Het laat ook niet toe om een offset te specificeren.
U kunt hiervoor een subquery gebruiken zoals
select *
from
( select *
from emp
order by sal desc )
where ROWNUM <= 5;
Kijk ook eens naar het onderwerp Over ROWNUM en het beperken van resultaten bij Oracle/AskTom voor meer informatie.
Update: Om het resultaat te beperken met zowel onder- als bovengrenzen wordt het wat opgeblazen met
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;
(Gekopieerd van opgegeven AskTom-artikel)
Update 2: Vanaf Oracle 12c (12.1) is er een syntax beschikbaar om rijen te beperken of te beginnen met offsets.
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;
Zie dit antwoord voor meer voorbeelden. Met dank aan Krumia voor de hint.
Een analytische oplossing met slechts één geneste query:
SELECT * FROM
(
SELECT t.*, Row_Number() OVER (ORDER BY name) MyRow FROM sometable t
)
WHERE MyRow BETWEEN 10 AND 20;
Rank()
kan worden vervangen door Row_Number()
maar kan meer records opleveren dan je verwacht als er dubbele waarden voor naam zijn.
(ongetest) iets als dit kan het werk doen
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
Er is ook de analytische functie rang, die je kunt gebruiken om te ordenen door.