How to Limit Oracle Query Results After Ordering?

Concisely Limiting Ordered Results in Oracle

Oracle's row limiting differs from simpler SQL dialects like MySQL. While MySQL uses LIMIT, Oracle employs the ROWNUM pseudocolumn. However, directly using ROWNUM in a WHERE clause before ORDER BY yields unpredictable results.

The Challenge: Ordering Before Limiting

The issue stems from ROWNUM's evaluation before the ORDER BY clause. This means you don't get the top N rows after sorting; instead, you get N arbitrary rows.

The Solution: Subqueries for Precise Control

To correctly limit rows after ordering, use a subquery:

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

This first orders the emp table by salary (sal) descending, then the outer query selects only the top 10 rows using ROWNUM.

Specifying Start and End Rows:

For more precise control, defining both upper and lower bounds requires nested subqueries:

<code class="language-sql">SELECT *
FROM (
  SELECT a.*, ROWNUM rnum
  FROM (
    SELECT * FROM emp ORDER BY sal DESC
  ) a
  WHERE ROWNUM <= 30
)
WHERE rnum >= 21;</code>

This retrieves rows 21-30 (inclusive) after sorting by salary.

Modern Oracle (12c and later): Simplified Syntax

Oracle 12c and later versions offer a cleaner approach:

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

-- Rows 20-30
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;</code>

This FETCH FIRST and OFFSET syntax is significantly more readable and efficient for pagination. Use this method if your Oracle version supports it.

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