using of rownum function with “>” sign in oracle [

问题:

This question already has an answer here:

• How do I limit the number of rows returned by an Oracle query after ordering? 14 answers

I want to select the number of rows which are greater than 3 by rownum function i_e "(rownum>3)" for example if there are 25 rows and I want to retrieve the last 22 rows by rownum function. but when I write the

select * from test_table where rownum>3;

it retrieve no row. can any one help me to solve this problem. thanks in advance

回答1:

In RDBMS there is no first or last rows. What you calls "raws" , actually is set(sets), they can be ordered or not. rownum is a function, which is just enumerates result set, it makes sense only after set is calculated, to order your set of data (rows) you should do it in your query before rownum call, you must tell DB what means for the order in particular select statement.

回答2:

It is not working because: for the first row assumes the ROWNUM of 1 and since your WHERE clause is ROWNUM>3 then this reduces to 1>3 and the row is discarded. The subsequent row will then be tested against a ROWNUM of 1 (since the previous row is no longer in the output and now does not require a row number), which will again fail the test and be discarded. Repeat, ad nauseum and all rows fail the WHERE clause filter and are discarded.

If you want to assign the rows a ROWNUM then you need to do this is a sub-query:

SELECT *                  -- Finally, in the outer query, filter on the assigned ROWNUM
FROM   (
  SELECT t.*,             -- First, in the inner sub-query, apply a ROWNUM
         ROWNUM AS rn
  FROM   test_table t
)
WHERE  rn > 3;

Or, if you want to order the results before numbering:

SELECT *                  -- Finally, in the outer query, filter on the assigned ROWNUM
FROM   (
  SELECT t.*,             -- Second, in the next level sub-query, apply a ROWNUM
         ROWNUM AS rn
  FROM   (
    SELECT *              -- First, in the inner-most sub-query, apply an order
    FROM   test_table
    ORDER BY some_column
  ) t
)
WHERE  rn > 3;

回答3:

select * from (select rownum as rn, t.* from test_table t) where rn > 3

see this article for more samples On Top-n and Pagination Queries By Tom Kyte