In SQL, how to limit the number of rows after ordering it in Oracle DB?

We can create a simple table order_test for demonstrating the solution.

CREATE  TABLE order_test (
    ID INT
);

INSERT INTO  order_test  VALUES( 1) ;
INSERT INTO  order_test  VALUES(3);
INSERT INTO  order_test VALUES(2) ;
INSERT INTO  order_test VALUES(5);
INSERT INTO  order_test VALUES(4) ;

The generic way of limiting the number of rows returned by an Oracle query after ordering is to use a subquery and ROWNUM. First, we order the data in the subquery and then use ROWNUM to limit the number of rows returned.

SELECT  *
FROM
(
    SELECT  *
    FROM order_test
    ORDER BY id
)
WHERE ROWNUM <= 4;

However, from version 12c, we have a new row limiting clause. We can limit rows using the keyword OFFSET and ROWS FETCH NEXT after ORDER BY as below.

SELECT *
FROM   order_test
ORDER BY id
OFFSET 1 ROWS FETCH NEXT 4 ROWS ONLY;

Up Next:

Read What is the difference between INNER JOIN and OUTER JOIN in SQL?