If I execute a query which returns 100 records but I want the record from 20 to 30.  How is it possible to filter the specific results as the row number can only return top records but what will I have to do if I want results from 30 to  40 or  etc and want to ignore some of the top records.

You first select 30 rows in a query using rownum<30 after that make another query for rownum<20
and now subtract second query from first query.

  Example:
          select * from emp where rownum<30
          minus
          select * from emp where rownum<20;

Muzammil Husain

How to select the alternative rows in the table?

Jaii

select * from emp where rownum <10;

*so the result will appear less then 10 record

Hazlina Kamilan

You cannot rely on rownum and what do you mean by alternate rows, as oracle doesn't store the records in any predifined sequence.

Sumit

This is not right. You can use the following

select rownum,sku from win_store where mod(rownum,2) = 0

Seetha

In Oracle, you can use the query as:
Select rownum, * From <tablename> where mod (rownum,2) <> 0

In SQL Server, you can use the query as:
Select id, * from <tablename> where (id % 2 <> 0 ).
Here id is the identity Column.

Ratikanta

This will always return 0 rows.

Sunil Bhardwaj

rownum won't work with any operator, other than <(less than).

So the query can be :

select b.* from (select rownum cnt, a.* from emp a) b where mod(cnt,2)<>0;

Manoj

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips