ROWNUM IN ORACLE

问题:

I want to retrive record at 4th position in ORACLE 9i. Can I compare ROWNUM=4 in the WHERE clause??

回答1:

No, ROWNUM is assigned after the WHERE clause is evaluated, so it cannot "skip" rownum one to three.

Furthermore, it is assigned BEFORE sorting.

This is the most annoying "feature" of Oracle. They really need to implement LIMIT/OFFSET.

You need to do something like

  select * from (
      select a.*, rownum rn from (
         select the_data from the_table order by the_order 
      ) a where rownum < 5
  ) where rn = 4

回答2:

SELECT * 
FROM  
( SELECT mt.mydata
  FROM myTable mt
  WHERE mt.myId = xxx
  ORDER BY mt.myDate DESC
) 
WHERE ROWNUM <= 10;

回答3:

why don't you try something like this :)

select * from (select rownum sn, tn.* from tablename tn ) where sn=4

here tablename is the name of your table and tn is the alias assigned to it.