At my workplace, we often like to page through query results. That is, we'd like to show results in bunches of, say, 50.
Some databases (none that we use: PostgreSQL and MySQL) do this using LIMIT and OFFSET:
select * from table
where blah = 2
order by thing1, thing2
limit 50 offset 100
That query would give you the 3rd "page" of 50 results. (Note: I believe MySQL has shorthand: LIMIT 50,100)
I think Omnidex (a funky flat file indexing engine we use) lets us do something like:
select top 50 skip 100 * from table
where blah = 2
order by thing1, thing2
SQL server has the TOP, but I have no idea how to do offset/skip...
Anyway, what I do know is my boss kept bugging me to figure out how to do it in Oracle now that we are playing with a new database on that platform.
So, thanks to the internets, I present you with the wonderfully wacky world of paging in Oracle:
Select * from (
select t1.*, ROWNUM rn from (
select * from table where blah = 2 order by thing1, thing2
) t1
) where rn between 101 and 150
Key bits:
- You have to alias the inner-most select if you want to select * (That's the "t1" jazz.)
- ROWNUM is a calculated field. Each select has it's own rownum column under the covers. This the real reason there are so many nested "views" to do this. The first select has rownum values that are calculated BEFORE the "order by". The second select has rownum values that are calculated AFTER the "order by" in the inner-most select -- this is the rownum set we actually want to filter, so we include it in the select (with an alias). The outer-most select is needed to actually apply that filter.
Caveat: My friendly neighborhood Oracle DBA tells me, "Rownum is virtual, you aren't guaranteed that 101 through 150 are the same every time."
Alternative: Said friendly neighborhood Oracle DBA came back with another method that I have yet to play with seems to work nicely:
Lets go this route, subtle difference, but there is a reason.
Select * from ( Select a.*, rownum rnum From ( select * from table where blah = 2 order by thing1, thing2 ) a where rownum <= 150 ) where rnum >= 101It has to do with the way Oracle processes the COUNT(STOPKEY). Also I had to remember that in 8.1 that stuff wouldn't work. With 9i and above we should be good to go.