-1

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

MT0
  • 86,097
  • 7
  • 42
  • 90
Muhammad Yasir
  • 27
  • 2
  • Try as `select * from test_table where rownum<=3` – Jacob Apr 11 '17 at 07:41
  • Have you tried searching? There are many similar questions, for example [this one](http://stackoverflow.com/questions/15091849/how-to-use-oracle-order-by-and-rownum-correctly). Also, you need to define how to decide if a row is "greater" than another – Aleksej Apr 11 '17 at 07:43
  • 1
    [Quote from the manual](https://docs.oracle.com/database/121/SQLRF/pseudocolumns009.htm#SQLRF00255) "*Conditions testing for ROWNUM values greater than a positive integer **are always false***" – a_horse_with_no_name Apr 11 '17 at 07:55
  • That's not how ROWNUM works. You could do this with the ROW_NUMBER analytic function but you will still need to decide what 'matters' in terms of a column to order it by. – BriteSponge Apr 11 '17 at 08:27

3 Answers3

2

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.

Seyran
  • 701
  • 4
  • 8
  • One way to achieve you result is - `select * from test_table minus select * from test_table where rownum<=3;` – Tom J Muthirenthi Apr 11 '17 at 08:01
  • 1
    @Tom J Muthirenthi And what does this result mean ? give "some rows", oh, no "some rows" is too much, give me 3 less. The word "Relational" in RDBMS must mean something, isn't it ? – Seyran Apr 11 '17 at 08:10
  • `ROWNUM` is not a function - it is a [pseudo-column](https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm). Also, you do not need to specify an order - if you do not then Oracle will number the rows in the same order as the rows were retrieved (which can lead to a non-deterministic ordering). – MT0 Apr 11 '17 at 08:50
  • "@MT0 can't agree with, Yes , according to Oracle documentation rownum is pseudocolumn, but treating them as a functions is not error. "Pseudocolumns" behaves like a table column that's why they called "pseudocolumns", but actually they are implemented as system functions. – Seyran Apr 11 '17 at 10:08
1

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;
MT0
  • 86,097
  • 7
  • 42
  • 90
0
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

are
  • 2,425
  • 2
  • 19
  • 26