So I want to select a range of rows in an Oracle DB. I need to do this because I have millions of rows in the table and I want to paginate results to the user (if you know another way to do this on the client side, I'm using JavaFX if it matters but I don't think it's a good idea to send all the datas over the network to paginate them on the client side).
So after reading this post: SQL ROWNUM how to return rows between a specific range, I have the following query:
Select * From (Select t.*, rownum r from PERSON t) Where r > 100 and r < 110;
The 100
and 110
are just example. In the application, I just ask for the lower bound and add a size of 10_000 to fetch the next 10_000 rows.
Now the rownum column appears in the result and I don't want to see it. As I'm not very experienced with SQL, here's my questions:
Why (this was my first attempt until I search on SO)
Select * From Person Where rownum > 100 and rownum < 110;
returns 0 rows ?Why there is no simple way to do something like
Select ... FROM ... WHERE rownum BETWEEN lowerBound AND upperBound
?How to get rid of the
r
column in the resulting values? From there SQL exclude a column using SELECT * [except columnA] FROM tableA? I need apparently to create a view or a temporary table, but is there another way considering my query?Does it ensure correct pagination? I read this article section "Pagination with ROWNUM", that said I should order the values by something unique to get a consistent pagination (so I guess ordering by
rownum
is fine, if you can confirm). Doesn't it defeat the purpose of usingFIRST_ROWS(N)
?
I hope it's not too much, I could split into separate questions, but I think it's relevant to have them collapsed as they are closely related.