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

Returning a Specific Range of ROWNUM Values in SQL

When working with data in SQL, it is often useful to be able to select a specific range of rows based on their row numbers. The ROWNUM pseudo-column provides a convenient way to reference the position of a row within its result set.

Problem:

Consider the following query:

select * from maps006 where rownum >49 and rownum <101

This query is intended to return all rows from the maps006 table with row numbers greater than 49 and less than 101. However, it only returns rows that satisfy the less-than (<) operator, ignoring the greater-than (>) operator.

Solution:

To correctly retrieve the rows within the specified range, you can use a subquery to create a temporary table that includes a new column called r for the row numbers. The following query achieves this:

SELECT * from
(
  select m.*, rownum r
  from maps006 m
)
where r > 49 and r < 101

• The inner subquery selects all columns (m.*) from the maps006 table and adds a new column named r that contains the row numbers.
• The outer query then filters the temporary table based on the desired range of row numbers.

By using this approach, you can effectively return the rows that fall within the specified ROWNUM range.

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