How to Select a Specific Range of Rows Using ROWNUM in SQL?

Returning a Specific Range of ROWNUM Values in SQL

Returning a specific range of ROWNUM values can be achieved by utilizing the ROWNUM pseudo-column along with subqueries. When using ROWNUM to limit the rows returned from a query, it is crucial to consider that the values are implicitly calculated and assigned based on the ordering of the data.

To retrieve rows within a particular range, the following approach can be employed:

SELECT *
FROM
(
  SELECT m.*, ROWNUM AS r
  FROM maps006 AS m
)
WHERE
  r > 49 AND r < 101

In this subquery, the original table maps006 is aliased as m. Additionally, the ROWNUM pseudo-column is introduced and aliased as r. This allows us to manipulate the ROWNUM values explicitly within the subquery.

The main query then utilizes the subquery to retrieve only those rows where the r column satisfies the given range criteria (r > 49 AND r < 101). This ensures that the rows returned fall within the specified range of ROWNUM values, successfully addressing the limitations encountered in the original query.

The above is the detailed content of How to Select a Specific Range of Rows Using ROWNUM in SQL?. For more information, please follow other related articles on the PHP Chinese website!