How to Limit Row Results in Oracle Queries After Ordering?

The number of results after sorting in Oracle database

Many database users rely on MySQL's Limit clauses to retrieve a specific range after sorting. However, Oracle itself does not support similar functions. Oracle uses the Rownum pseudo column, which is calculated before Order By. When trying to limit the number of lines according to sorting, this behavior may cause accidents.

In order to implement the restrictions similar to MySQL in Oracle, a solution is a sub -query. Please consider the following example:

This query will be retrieved in the top 5 lines in the SAL column.

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

In order to improve flexibility, you can use the following methods:

This son query allows you to specify the lower and upper limit of the search bank.

SELECT *
FROM (
    SELECT *, ROWNUM AS rnum
    FROM (
        SELECT *
        FROM emp
        ORDER BY sal DESC
    ) AS a
)
WHERE rnum >= :MIN_ROW_TO_FETCH AND rnum <= :MAX_ROW_TO_FETCH;

In addition, Oracle 12C (12.1) introduces a new grammar:

This syntax provides a more concise way to limit the number of results according to the sorting limit.

-- 获取前10个结果
SELECT *
FROM sometable
ORDER BY name
FETCH FIRST 10 ROWS ONLY;

-- 获取第20-30个结果行
SELECT *
FROM sometable
ORDER BY name
OFFSET 20 ROWS FETCH NEXT 10 ROWS ONLY;

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