| rownum [message #21075] |
Wed, 10 July 2002 17:53  |
vikas mittal
Messages: 1
Registered: July 2002
|
Junior Member |
|
|
i want to fetch suppose 5th row from a table in db and i write
select * from table_x where rownum=5 then it says no row found also i want to know does max or min functions work with rownum
|
|
|
|
| Re: rownum [message #21076 is a reply to message #21075] |
Wed, 10 July 2002 18:05  |
Su
Messages: 154
Registered: April 2002
|
Senior Member |
|
|
I am not sure whether it is a bug in Oracle, but the equal sign with rownum does not work properly. You try it out alternatively with > and < operators.
SELECT * FROM TABLE_X WHERE ROWNUM>4 AND ROWNUM<6;
Try it out. Good luck :)
|
|
|
|
| Re: rownum [message #21079 is a reply to message #21075] |
Wed, 10 July 2002 21:36 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
Rownum will never be equal to any number except 1 (and not even this is guaranteed depending on the number of rows in the table and the WHERE clause).
Usually, when someone asks for the "5th" row, it is based on some criteria (the fifth highest salary, the fifth youngest person, etc.). Without any such criteria, rownum is arbitrary.
This is the usual approach in 8i and later for a TOP-N or nth type analysis:
select *
from (select t.*, rownum rn
from t
order by some_column)
where rn = 5; -- for top 5, change = to <= |
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
