How to Limit Rows in Oracle Queries After Ordering?

Restricting Result Sets in Ordered Oracle Queries

Oracle's ROWNUM pseudocolumn differs from MySQL's LIMIT clause; ROWNUM is assigned before the ORDER BY clause is processed. Therefore, directly using ROWNUM won't limit rows after ordering. To achieve this, utilize a subquery:

<code class="language-sql">SELECT *
FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= n;</code>

This approach first orders the emp table by salary (sal) descending, then the outer query uses ROWNUM to restrict the output to the top n rows.

For more intricate scenarios requiring both upper and lower bounds, a nested subquery is necessary:

<code class="language-sql">SELECT *
FROM (SELECT a.*, ROWNUM rnum
      FROM (SELECT * FROM your_table ORDER BY your_column) a
      WHERE ROWNUM <= :MAX_ROW_TO_FETCH)
WHERE rnum >= :MIN_ROW_TO_FETCH;</code>

Oracle 12c and Beyond

Oracle 12c (and later versions) offer a more streamlined syntax using FETCH FIRST and OFFSET:

<code class="language-sql">SELECT *
FROM sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;</code>

This directly limits the result set to the first 10 rows after ordering by name. To specify an offset (e.g., skip the first 20 rows and retrieve the next 10), use the OFFSET clause:

<code class="language-sql">SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;</code>

Refer to the official Oracle documentation for comprehensive examples and performance optimization strategies.

The above is the detailed content of How to Limit Rows in Oracle Queries After Ordering?. For more information, please follow other related articles on the PHP Chinese website!