Can one retrieve only rows X to Y from a table?

Submitted by admin on Wed, 2005-12-14 23:34
Body: 

Shaik Khaleel provided this solution to the problem:

SELECT * FROM (
   SELECT ename, rownum rn 
            FROM emp WHERE rownum < 101
) WHERE  RN between 91 and 100 ;

Note: the 101 is just one greater than the maximum row of the required rows (means x= 90, y=100, so the inner values is y+1).

Ravi Pachalla provided this solution:

SELECT rownum, f1 FROM t1
GROUP BY rownum, f1 HAVING rownum BETWEEN 2 AND 4;

Another solution is to use the MINUS operation. For example, to display rows 5 to 7, construct a query like this:

SELECT *
FROM   tableX
WHERE  rowid in (
   SELECT rowid FROM tableX
    WHERE rownum <= 7
  MINUS
   SELECT rowid FROM tableX
   WHERE rownum < 5);

Youssef Youssef provided this soluton: "this one was faster for me and allowed for sorting before filtering by rownum. The inner query (table A) can be a series of tables joined together with any operation before the filtering by rownum is applied."

SELECT * 
  FROM (SELECT a.*, rownum RN 
	  FROM (SELECT * 
                         FROM t1 ORDER BY key_column) a
         WHERE rownum <=7)
 WHERE rn >=5

Please note, there is no explicit row order in a relational database. However, this query is quite fun and may even help in the odd situation.

»

Comments

Submitted by Grayson (not verified) on Fri, 2005-12-30 02:38. 

select * from
(select rownum rm,t.* from fwlot t where rownum<=y)
where rm between x and y

select * from
(select row_number() over(order by 1) rm,t.*
  from  t
) 
where rm between x and y

»

Submitted by PaulM (not verified) on Wed, 2006-10-04 14:51.

The above query that uses MINUS will return two rows, but since neither of the subqueries above or below the minus have an "order by" clause, the rows are random. You might as well use
select * from tableX
where rownum < 3;

»

Submitted by Bhu_Net on Thu, 2009-01-22 08:13.

create or replace procedure Sp_alter(res out number)
as
begin

alter table question add partition :old.module_name values less than(:old.module_code);
res:=1;

end;

what is error in this code

»