Why = operator doesn't work with ROWNUM other than for value 1?

Question

I have the following query:

select * from abc where rownum = 10

Output: No records to display

I sure have more than 25 records in the abc table and my objective is to display the nth record.

If I write the query as: -

select * from abc where rownum = 1

it works fine and gives me the first record. Not any other record other than first.

Any idea?

Answer 1

Because row numbers are assigned sequentially to the rows that are fetched and returned.

Here's how that statement of yours works. It grabs the first candidate row and temporarily gives it row number 1, which doesn't match your condition so it's thrown away.

Then you get the second candidate row and it's also given row number 1 (since the previous one was tossed away). It doesn't match either.

Then the third candidate row ... well, I'm sure you can see where this is going now. In short, you will never find a row that satisfies that condition.

Row numbers are only useful for = 1, < something or <= something.

This is all explained in the Oracle docs for the rownum pseudo-column.

You should also keep in mind that SQL is a relational algebra that returns unordered sets unless you specify an order. That means row number ten may be something now and something else in three minutes.

If you want a (kludgy, admittedly) way to get the nth row, you can use something like (for the fifth row):

select * from (
    select * from (
        select col1, col2, col3 from tbl order by col1 asc
    ) where rownum < 6 order by col1 desc
) where rownum = 1

The inner select will ensure you have a consistent order on the query before you start throwing away rows, and the middle select will throw away all but the first five rows from that, and also reverse the order.

The outer select will then only return the first row of the reversed set (which is the last row of the five-row set when it was in ascending order).

A better way is probably:

select * from (
    select rownum rn, col1, col2, col3 from tbl order by col1
) where rn = 5

This works by retrieving everything and assigning the rownum to a "real" column, then using that real column number to filter the results.