Pagination

Have you tried any query like this?
select * from time_dim where rownum between 1 and 5
What will be the answer?

Okay, what will be the answer for this one?
select * from time_dim where rownum between 2 and 5

Surprised? Rownum will return any row only if it starts with 1. Everything else will not return anything.

Okay, I guess you knew this part already. Then try this query.
select * from time_dim where rownum between 1 and 5 order by time_key asc

Do you think you will get the first 5 ranks ordered by time_key? Try it. You didnt. You got some 5 records ordered by time_key, not necessarily the top 5.

Why? Because rownum is a pseudo column that is assigned to rows that are fetched. The first 5 records that got fetched were returned with order by on time_key.

Then how do you make sure that you get the first 5 only?

For that you need something called pagination. Try this query
select * from
(
select rownum as rank, a.* from
(
select * from time_dim order by time_key
) a
) b
where rank=8

Here you passed ordered the data from the query in a sub-query. Then passed it to the outer one with the rownum and gave it an alias. Then you used it in the outer query to get that rank.

It is necessary to first order the data and then pass it to an outer query to use the rownum. If you use rownum from the same query where you ordered, it will not be ordered correctly.