Oracle order by undetermined

Question

I'm trying to add pagination to a simple select query, like

select rownum rownum_,t.* from (select id,name from table) t
where rownum <=4

However the above query behaves differently when where condition is there and where condition is not there. But on removing the name column, the behaviour is consistent. Can anyone tell why?

I understand rownum is determined when query is run, what I'm not able to understand is that a where condition with rownum <=n , changes the order

Answer 1

You have to understand that rownum is a pseudo column.

The rownum column in your query indicates the order ( first record, second, third... )

Then the records order returned by

select id,name from table

is not guaranteed. In your request, the rownum associated to the first record will be always 1 despict the fact that the first record might be different every time.

In conclusion, always use ORDER BY when you want a specific order.