How to Retrieve a Specific Range of Rows Using SQL's ROWNUM?

Retrieving a Specific Range of SQL ROWNUM Values

In SQL, the ROWNUM pseudocolumn can be used to retrieve the current row's position in the result set. It is often useful when returning a limited range of rows. However, your current query seems to be encountering issues with returning rows within a specific range.

To rectify this, a slight modification is needed. Instead of using the standard ROWNUM pseudocolumn, you should use a subquery to create a temporary table or CTE (Common Table Expression) that includes both the original data and the row numbers. This approach ensures that the row numbers are evaluated before the filtering conditions are applied.

The corrected query would look like this:

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

In this query:

• ROWNUM() OVER (ORDER BY m.id) creates a new column named r that contains the row numbers for each row in the maps006 table, ordered by the id column.
• The subquery is aliased as m to match the original table name.
• The main query then filters the subquery result using the desired range for r (i.e., rows greater than 49 and less than 101).

This revised query should successfully retrieve the rows within the specified ROWNUM range.

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