How do I limit the number of rows returned by an Oracle query after ordering

Question

Is there a way to make an Oracle query behave like it contains a MySQL limit clause  In MySQL  I can do this  select    from sometable order by name limit 20 10  to get the 21st to the 30th rows  skip the first 20  give the next 10   The rows are selected after the order by  so it really starts on the 20th name alphabetically  In Oracle  the only thing people mention is the rownum pseudo-column  but it is evaluated before order by  which means this  select    from sometable where rownum  lt   10 order by name  will return a random set of ten rows ordered by name  which is not usually what I want  It also doesn t allow for specifying an offset

User · Answer

SQL Standard

Since version 12c Oracle supports the SQL:2008 Standard, which provides the following syntax to limit the SQL result set:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g and older versions

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

User · Answer

select   FROM  SELECT     ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID    FROM EMP   EMP  where ROWID 5   greater then values find out    select   FROM  SELECT         ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID        FROM EMP   EMP  where ROWID gt 5   less then values find out  select   FROM  SELECT         ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID        FROM EMP   EMP  where ROWID 5

User · Answer

Starting from Oracle 12c R1  12 1   there is a row limiting clause  It does not use familiar LIMIT syntax  but it can do the job better with more options  You can find the full syntax here   Also read more on how this works internally in Oracle in this answer   To answer the original question  here s the query  SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    For earlier Oracle versions  please refer to other answers in this question   Examples  Following examples were quoted from linked page  in the hope of preventing link rot  Setup CREATE TABLE rownum order test     val  NUMBER     INSERT ALL   INTO rownum order test SELECT level FROM   dual CONNECT BY level  lt   10   COMMIT   What s in the table  SELECT val FROM   rownum order test ORDER BY val          VAL ----------          1          1          2          2          3          3          4          4          5          5          6          6          7          7          8          8          9          9         10         10  20 rows selected   Get first N rows SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY          VAL ----------         10         10          9          9          8  5 rows selected   Get first N rows  if Nth row has ties  get all the tied rows SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS WITH TIES          VAL ----------         10         10          9          9          8          8  6 rows selected   Top x  of rows SELECT val FROM   rownum order test ORDER BY val FETCH FIRST 20 PERCENT ROWS ONLY          VAL ----------          1          1          2          2  4 rows selected   Using an offset  very useful for pagination SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY          VAL ----------          3          3          4          4  4 rows selected   You can combine offset with percentages SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY          VAL ----------          3          3          4          4  4 rows selected

User · Answer

Starting from Oracle 12c R1  12 1   there is a row limiting clause  It does not use familiar LIMIT syntax  but it can do the job better with more options  You can find the full syntax here   Also read more on how this works internally in Oracle in this answer   To answer the original question  here s the query  SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    For earlier Oracle versions  please refer to other answers in this question   Examples  Following examples were quoted from linked page  in the hope of preventing link rot  Setup CREATE TABLE rownum order test     val  NUMBER     INSERT ALL   INTO rownum order test SELECT level FROM   dual CONNECT BY level  lt   10   COMMIT   What s in the table  SELECT val FROM   rownum order test ORDER BY val          VAL ----------          1          1          2          2          3          3          4          4          5          5          6          6          7          7          8          8          9          9         10         10  20 rows selected   Get first N rows SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY          VAL ----------         10         10          9          9          8  5 rows selected   Get first N rows  if Nth row has ties  get all the tied rows SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS WITH TIES          VAL ----------         10         10          9          9          8          8  6 rows selected   Top x  of rows SELECT val FROM   rownum order test ORDER BY val FETCH FIRST 20 PERCENT ROWS ONLY          VAL ----------          1          1          2          2  4 rows selected   Using an offset  very useful for pagination SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY          VAL ----------          3          3          4          4  4 rows selected   You can combine offset with percentages SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 20 PERCENT ROWS ONLY          VAL ----------          3          3          4          4  4 rows selected

User · Answer

For each row returned by a query  the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows  The first row selected has a ROWNUM of 1  the second has 2  and so on     SELECT   FROM sometable1 so     WHERE so id IN       SELECT so2 id from sometable2 so2     WHERE ROWNUM  lt  5           AND ORDER BY so somefield AND ROWNUM  lt   100    I have implemented this in oracle server 11 2 0 1 0

User · Answer

select   FROM  SELECT     ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID    FROM EMP   EMP  where ROWID 5   greater then values find out    select   FROM  SELECT         ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID        FROM EMP   EMP  where ROWID gt 5   less then values find out  select   FROM  SELECT         ROW NUMBER   OVER  ORDER BY sal desc    AS ROWID        FROM EMP   EMP  where ROWID 5

User · Answer

Less SELECT statements  Also  less performance consuming  Credits to  anibal upf br  SELECT       FROM    SELECT t                       rownum AS rn             FROM   shhospede t  a     WHERE  a rn  gt   in first     AND    a rn  lt   in first

User · Answer

I v started preparing for Oracle 1z0-047 exam  validated against 12c While prepping for it i came across a 12c enhancement known as  FETCH FIRST  It enables you to fetch rows  limit rows as per your convenience  Several options are available with it  - FETCH FIRST n ROWS ONLY  - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY    leave the n rows and display next N1 rows  - n   rows via FETCH FIRST N PERCENT ROWS ONLY   Example    Select   from XYZ a order by a pqr FETCH FIRST 10 ROWS ONLY

User · Answer

You can use a subquery for this like  select   from     select      from emp    order by sal desc    where ROWNUM  lt   5    Have also a look at the topic On ROWNUM and limiting results at Oracle AskTom for more information   Update  To limit the result with both lower and upper bounds things get a bit more bloated with  select   from    select a    ROWNUM rnum from       lt your query goes here  with order by gt    a    where ROWNUM  lt    MAX ROW TO FETCH   where rnum   gt    MIN ROW TO FETCH     Copied from specified AskTom-article   Update 2  Starting with Oracle 12c  12 1  there is a syntax available to limit rows or start at offsets   SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    See this answer for more examples  Thanks to Krumia for the hint

User · Answer

For each row returned by a query  the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows  The first row selected has a ROWNUM of 1  the second has 2  and so on     SELECT   FROM sometable1 so     WHERE so id IN       SELECT so2 id from sometable2 so2     WHERE ROWNUM  lt  5           AND ORDER BY so somefield AND ROWNUM  lt   100    I have implemented this in oracle server 11 2 0 1 0

User · Answer

An analytic solution with only one nested query   SELECT   FROM      SELECT t    Row Number   OVER  ORDER BY name  MyRow FROM sometable t    WHERE MyRow BETWEEN 10 AND 20    Rank   could be substituted for Row Number   but might return more records than you are expecting if there are duplicate values for name

User · Answer

As an extension of accepted answer Oracle internally uses ROW NUMBER RANK functions  OFFSET FETCH syntax is a syntax sugar   It could be observed by using DBMS UTILITY EXPAND SQL TEXT procedure   Preparing sample   CREATE TABLE rownum order test     val  NUMBER     INSERT ALL   INTO rownum order test SELECT level FROM   dual CONNECT BY level  lt   10  COMMIT    Query   SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY    is regular   SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                  ROW NUMBER   OVER   ORDER BY  A2   VAL  DESC    rowlimit    rownumber         FROM  ROWNUM ORDER TEST   A2    A1   WHERE  A1   rowlimit    rownumber  lt  5 ORDER BY  A1   rowlimit   0  DESC    db lt  fiddle demo  Fetching expanded SQL text   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val           FROM   rownum order test           ORDER BY val DESC           FETCH FIRST 5 ROWS ONLY           output sql text   gt  x      dbms output put line x   end        WITH TIES is expanded as RANK   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val           FROM   rownum order test           ORDER BY val DESC           FETCH FIRST 5 ROWS WITH TIES           output sql text   gt  x      dbms output put line x   end     SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                 RANK   OVER   ORDER BY  A2   VAL  DESC    rowlimit    rank          FROM  ROWNUM ORDER TEST   A2    A1   WHERE  A1   rowlimit    rank  lt  5 ORDER BY  A1   rowlimit   0  DESC   and offset   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY           output sql text   gt  x      dbms output put line x   end      SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                ROW NUMBER   OVER   ORDER BY  A2   VAL    rowlimit    rownumber          FROM  ROWNUM ORDER TEST   A2    A1          WHERE  A1   rowlimit    rownumber  lt  CASE  WHEN  4 gt  0  THEN FLOOR TO NUMBER 4                 ELSE 0 END  4 AND  A1   rowlimit    rownumber  gt 4  ORDER BY  A1   rowlimit   0

User · Answer

In case of SQL-Developer  it automatically fetches only first 50 rows  And if we scroll down  it fetches another 50 rows and so on    Hence we dont need to define  in case of sql-developer tool

User · Answer

An analytic solution with only one nested query   SELECT   FROM      SELECT t    Row Number   OVER  ORDER BY name  MyRow FROM sometable t    WHERE MyRow BETWEEN 10 AND 20    Rank   could be substituted for Row Number   but might return more records than you are expecting if there are duplicate values for name

User · Answer

In case of SQL-Developer  it automatically fetches only first 50 rows  And if we scroll down  it fetches another 50 rows and so on    Hence we dont need to define  in case of sql-developer tool

User · Answer

If you are not on Oracle 12C  you can use TOP N query like below   SELECT    FROM      SELECT rownum rnum             a          FROM sometable a     ORDER BY name      WHERE rnum BETWEEN 10 AND 20    You can even move this from clause in with clause as follows  WITH b AS   SELECT rownum rnum         a       FROM sometable a ORDER BY name    SELECT   FROM b  WHERE rnum BETWEEN 10 AND 20    Here actually we are creating a inline view and renaming rownum as rnum  You can use rnum in main query as filter criteria

User · Answer

An analytic solution with only one nested query   SELECT   FROM      SELECT t    Row Number   OVER  ORDER BY name  MyRow FROM sometable t    WHERE MyRow BETWEEN 10 AND 20    Rank   could be substituted for Row Number   but might return more records than you are expecting if there are duplicate values for name

User · Answer

SQL Standard

Since version 12c Oracle supports the SQL:2008 Standard, which provides the following syntax to limit the SQL result set:

SELECT
    title
FROM
    post
ORDER BY
    id DESC
FETCH FIRST 50 ROWS ONLY

Oracle 11g and older versions

Prior to version 12c, to fetch the Top-N records, you had to use a derived table and the ROWNUM pseudocolumn:

SELECT *
FROM (
    SELECT
        title
    FROM
        post
    ORDER BY
        id DESC
)
WHERE ROWNUM <= 50

User · Answer

I did some performance testing for the following approaches   Asktom  select   from     select a    ROWNUM rnum from        lt select statement with order by clause gt      a where rownum  lt   MAX ROW   where rnum  gt   MIN ROW   Analytical  select   from      lt select statement with order by clause gt    where myrow between MIN ROW and MAX ROW   Short Alternative  select   from     select statement  rownum as RN with order by clause   where a rn  gt   MIN ROW and a rn  lt   MAX ROW   Results  Table had 10 million records  sort was on an unindexed datetime row    Explain plan showed same value for all three selects  323168  But the winner is AskTom  with analytic following close behind    Selecting first 10 rows took    AskTom  28-30 seconds Analytical  33-37 seconds Short alternative  110-140 seconds   Selecting rows between 100 000 and 100 010    AskTom  60 seconds Analytical  100 seconds   Selecting rows between 9 000 000 and 9 000 010    AskTom  130 seconds Analytical  150 seconds

User · Answer

You can use a subquery for this like  select   from     select      from emp    order by sal desc    where ROWNUM  lt   5    Have also a look at the topic On ROWNUM and limiting results at Oracle AskTom for more information   Update  To limit the result with both lower and upper bounds things get a bit more bloated with  select   from    select a    ROWNUM rnum from       lt your query goes here  with order by gt    a    where ROWNUM  lt    MAX ROW TO FETCH   where rnum   gt    MIN ROW TO FETCH     Copied from specified AskTom-article   Update 2  Starting with Oracle 12c  12 1  there is a syntax available to limit rows or start at offsets   SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    See this answer for more examples  Thanks to Krumia for the hint

User · Answer

On Oracle 12c  see row limiting clause in SQL reference    SELECT    FROM sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

User · Answer

Less SELECT statements  Also  less performance consuming  Credits to  anibal upf br  SELECT       FROM    SELECT t                       rownum AS rn             FROM   shhospede t  a     WHERE  a rn  gt   in first     AND    a rn  lt   in first

User · Answer

Pagination queries with ordering are really tricky in Oracle    Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views    ROWNUM is a pseudocolumn that gets many people into trouble  A ROWNUM value is not permanently assigned to a row  this is a common misunderstanding   It may be confusing when a ROWNUM value is actually assigned  A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting    What is more  a ROWNUM value is incremented only after it is assigned   This is why the followin query returns no rows    select     from  select          from some table        order by some column   where ROWNUM  lt   4 and ROWNUM  gt  1     The first row of the query result does not pass ROWNUM   1 predicate  so ROWNUM does not increment to 2  For this reason  no ROWNUM value gets greater than 1  consequently  the query returns no rows   Correctly defined query should look like this   select   from  select    ROWNUM rnum       from  select               from skijump results             order by points        where ROWNUM  lt   4  where rnum  gt  1     Find out more about pagination queries in my articles on Vertabelo blog    Oracle ROWNUM Explained Top-N and pagination queries

User · Answer

As an extension of accepted answer Oracle internally uses ROW NUMBER RANK functions  OFFSET FETCH syntax is a syntax sugar   It could be observed by using DBMS UTILITY EXPAND SQL TEXT procedure   Preparing sample   CREATE TABLE rownum order test     val  NUMBER     INSERT ALL   INTO rownum order test SELECT level FROM   dual CONNECT BY level  lt   10  COMMIT    Query   SELECT val FROM   rownum order test ORDER BY val DESC FETCH FIRST 5 ROWS ONLY    is regular   SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                  ROW NUMBER   OVER   ORDER BY  A2   VAL  DESC    rowlimit    rownumber         FROM  ROWNUM ORDER TEST   A2    A1   WHERE  A1   rowlimit    rownumber  lt  5 ORDER BY  A1   rowlimit   0  DESC    db lt  fiddle demo  Fetching expanded SQL text   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val           FROM   rownum order test           ORDER BY val DESC           FETCH FIRST 5 ROWS ONLY           output sql text   gt  x      dbms output put line x   end        WITH TIES is expanded as RANK   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val           FROM   rownum order test           ORDER BY val DESC           FETCH FIRST 5 ROWS WITH TIES           output sql text   gt  x      dbms output put line x   end     SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                 RANK   OVER   ORDER BY  A2   VAL  DESC    rowlimit    rank          FROM  ROWNUM ORDER TEST   A2    A1   WHERE  A1   rowlimit    rank  lt  5 ORDER BY  A1   rowlimit   0  DESC   and offset   declare   x VARCHAR2 1000   begin  dbms utility expand sql text          input sql text   gt              SELECT val FROM   rownum order test ORDER BY val OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY           output sql text   gt  x      dbms output put line x   end      SELECT  A1   VAL   VAL   FROM   SELECT  A2   VAL   VAL   A2   VAL   rowlimit   0                ROW NUMBER   OVER   ORDER BY  A2   VAL    rowlimit    rownumber          FROM  ROWNUM ORDER TEST   A2    A1          WHERE  A1   rowlimit    rownumber  lt  CASE  WHEN  4 gt  0  THEN FLOOR TO NUMBER 4                 ELSE 0 END  4 AND  A1   rowlimit    rownumber  gt 4  ORDER BY  A1   rowlimit   0

User · Answer

An analytic solution with only one nested query   SELECT   FROM      SELECT t    Row Number   OVER  ORDER BY name  MyRow FROM sometable t    WHERE MyRow BETWEEN 10 AND 20    Rank   could be substituted for Row Number   but might return more records than you are expecting if there are duplicate values for name

User · Answer

On Oracle 12c  see row limiting clause in SQL reference    SELECT    FROM sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY

User · Answer

untested  something like this may do the job  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  lt  30     order by name              -- in the desired order   select                         -- then get rows 21    30 from twenty where rownum  gt  20 order by name                  -- in the desired order   There is also the analytic function rank  that you can use to order by

User · Answer

untested  something like this may do the job  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  lt  30     order by name              -- in the desired order   select                         -- then get rows 21    30 from twenty where rownum  gt  20 order by name                  -- in the desired order   There is also the analytic function rank  that you can use to order by

User · Answer

I did some performance testing for the following approaches   Asktom  select   from     select a    ROWNUM rnum from        lt select statement with order by clause gt      a where rownum  lt   MAX ROW   where rnum  gt   MIN ROW   Analytical  select   from      lt select statement with order by clause gt    where myrow between MIN ROW and MAX ROW   Short Alternative  select   from     select statement  rownum as RN with order by clause   where a rn  gt   MIN ROW and a rn  lt   MAX ROW   Results  Table had 10 million records  sort was on an unindexed datetime row    Explain plan showed same value for all three selects  323168  But the winner is AskTom  with analytic following close behind    Selecting first 10 rows took    AskTom  28-30 seconds Analytical  33-37 seconds Short alternative  110-140 seconds   Selecting rows between 100 000 and 100 010    AskTom  60 seconds Analytical  100 seconds   Selecting rows between 9 000 000 and 9 000 010    AskTom  130 seconds Analytical  150 seconds

User · Answer

untested  something like this may do the job  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  lt  30     order by name              -- in the desired order   select                         -- then get rows 21    30 from twenty where rownum  gt  20 order by name                  -- in the desired order   There is also the analytic function rank  that you can use to order by

User · Answer

Pagination queries with ordering are really tricky in Oracle    Oracle provides a ROWNUM pseudocolumn that returns a number indicating the order in which the database selects the row from a table or set of joined views    ROWNUM is a pseudocolumn that gets many people into trouble  A ROWNUM value is not permanently assigned to a row  this is a common misunderstanding   It may be confusing when a ROWNUM value is actually assigned  A ROWNUM value is assigned to a row after it passes filter predicates of the query but before query aggregation or sorting    What is more  a ROWNUM value is incremented only after it is assigned   This is why the followin query returns no rows    select     from  select          from some table        order by some column   where ROWNUM  lt   4 and ROWNUM  gt  1     The first row of the query result does not pass ROWNUM   1 predicate  so ROWNUM does not increment to 2  For this reason  no ROWNUM value gets greater than 1  consequently  the query returns no rows   Correctly defined query should look like this   select   from  select    ROWNUM rnum       from  select               from skijump results             order by points        where ROWNUM  lt   4  where rnum  gt  1     Find out more about pagination queries in my articles on Vertabelo blog    Oracle ROWNUM Explained Top-N and pagination queries

User · Answer

I v started preparing for Oracle 1z0-047 exam  validated against 12c While prepping for it i came across a 12c enhancement known as  FETCH FIRST  It enables you to fetch rows  limit rows as per your convenience  Several options are available with it  - FETCH FIRST n ROWS ONLY  - OFFSET n ROWS FETCH NEXT N1 ROWS ONLY    leave the n rows and display next N1 rows  - n   rows via FETCH FIRST N PERCENT ROWS ONLY   Example    Select   from XYZ a order by a pqr FETCH FIRST 10 ROWS ONLY

User · Answer

You can use a subquery for this like  select   from     select      from emp    order by sal desc    where ROWNUM  lt   5    Have also a look at the topic On ROWNUM and limiting results at Oracle AskTom for more information   Update  To limit the result with both lower and upper bounds things get a bit more bloated with  select   from    select a    ROWNUM rnum from       lt your query goes here  with order by gt    a    where ROWNUM  lt    MAX ROW TO FETCH   where rnum   gt    MIN ROW TO FETCH     Copied from specified AskTom-article   Update 2  Starting with Oracle 12c  12 1  there is a syntax available to limit rows or start at offsets   SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    See this answer for more examples  Thanks to Krumia for the hint

User · Answer

You can use a subquery for this like  select   from     select      from emp    order by sal desc    where ROWNUM  lt   5    Have also a look at the topic On ROWNUM and limiting results at Oracle AskTom for more information   Update  To limit the result with both lower and upper bounds things get a bit more bloated with  select   from    select a    ROWNUM rnum from       lt your query goes here  with order by gt    a    where ROWNUM  lt    MAX ROW TO FETCH   where rnum   gt    MIN ROW TO FETCH     Copied from specified AskTom-article   Update 2  Starting with Oracle 12c  12 1  there is a syntax available to limit rows or start at offsets   SELECT    FROM   sometable ORDER BY name OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY    See this answer for more examples  Thanks to Krumia for the hint

User · Answer

untested  something like this may do the job  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  lt  30     order by name              -- in the desired order   select                         -- then get rows 21    30 from twenty where rownum  gt  20 order by name                  -- in the desired order   There is also the analytic function rank  that you can use to order by

User · Answer

If you are not on Oracle 12C  you can use TOP N query like below   SELECT    FROM      SELECT rownum rnum             a          FROM sometable a     ORDER BY name      WHERE rnum BETWEEN 10 AND 20    You can even move this from clause in with clause as follows  WITH b AS   SELECT rownum rnum         a       FROM sometable a ORDER BY name    SELECT   FROM b  WHERE rnum BETWEEN 10 AND 20    Here actually we are creating a inline view and renaming rownum as rnum  You can use rnum in main query as filter criteria

[sql] How do I limit the number of rows returned by an Oracle query after ordering?

SQL Standard

Oracle 11g and older versions

Examples related to sql

Examples related to oracle

Examples related to sql-limit

[sql] How do I limit the number of rows returned by an Oracle query after ordering?

SQL Standard

Oracle 11g and older versions

Examples related to sql

Examples related to oracle

Examples related to pagination

Examples related to sql-limit