How do I limit the number of rows returned by an Oracle query af

ghz 8months ago ⋅ 124 views

How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause?

In MySQL, I can do this:

select * 
from sometable
order by name
limit 20,10

to get the 21st to the 30th rows (skip the first 20, give the next 10). The rows are selected after the order by, so it really starts on the 20th name alphabetically.

In Oracle, the only thing people mention is the rownum pseudo-column, but it is evaluated before order by, which means this:

select * 
from sometable
where rownum <= 10
order by name

will return a random set of ten rows ordered by name, which is not usually what I want. It also doesn't allow for specifying an offset.

Answers

In Oracle, you can achieve a similar result to the MySQL LIMIT clause using a combination of the ROWNUM pseudo-column and subqueries or common table expressions (CTEs). However, as you mentioned, you need to apply the ROWNUM condition after the ordering is applied. Here's how you can accomplish this:

SELECT *
FROM (
    SELECT t.*, ROWNUM AS rn
    FROM (
        SELECT *
        FROM sometable
        ORDER BY name
    ) t
    WHERE ROWNUM <= 30 -- Adjust this value to the end of your range
)
WHERE rn > 20; -- Adjust this value to the start of your range

Explanation:

  1. The innermost subquery orders the result set based on the name column.
  2. The next level subquery assigns a ROWNUM to each row.
  3. Finally, the outermost query filters rows based on the ROWNUM assigned in the subquery to achieve the desired offset and limit behavior.

In this example, rows 21 to 30 (inclusive) will be returned, which corresponds to the 21st to the 30th rows after ordering by the name column. Adjust the ROWNUM conditions according to your specific offset and limit requirements.

#oracle