How to Effectively Emulate MySQL's LIMIT Clause in Oracle SQL Queries?

Replicating MySQL's LIMIT in Oracle SQL

MySQL's LIMIT clause simplifies retrieving a specified number of rows after ordering, crucial for paginating data. Oracle, however, requires a different approach.

Why ROWNUM Isn't Enough

While Oracle's ROWNUM pseudocolumn might seem like a solution, it's applied before the ORDER BY clause. This means using ROWNUM for a range of ordered rows yields unpredictable results.

Effective Subquery Approach

A reliable method involves a subquery to order the data first, then apply ROWNUM for limiting:

SELECT *
FROM (
    SELECT *
    FROM emp
    ORDER BY sal DESC
)
WHERE ROWNUM <= 5;

Defining Upper and Lower Bounds

For more precise control, specifying both minimum and maximum row numbers necessitates a more complex query:

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;

Oracle 12c and Later Enhancements

Oracle 12c and later versions offer a cleaner solution using FETCH FIRST and OFFSET:

-- Retrieve the first 10 rows
SELECT *
FROM sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- Retrieve rows 20-30
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

This streamlined syntax provides a more intuitive way to manage row limits and offsets in Oracle.

The above is the detailed content of How to Effectively Emulate MySQL's LIMIT Clause in Oracle SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!