Paging w/o cursor or temp table?

I have an application that needs to be able to deliver a list from a database, a page at a time. For example, "all names starting with S." For various reasons, the application cannot hold open a cursor while waiting for a user to ask for the next page, and a temp table is too expensive. Also, I can't afford to keep the whole result set in memory (all names starting with S, in a million person table, would be a big result set) and I don't even want to fetch the whole result set unless the user needs it. So the question is... how do I do this? When the user hits "next", what is the best approach? Also assume that the key is composite... IE it has multiple fields (in this example: last, first, middle). The first query could be a simple: select * from table where lastname >= 'S' and lastname < 'T';For the "next" query, I could do: select * from table where ((lastname > 'Smith') or (lastname = 'Smith' and firstname > 'fred' or (Lastname = 'Smith' and firstname = 'fred' middlename >= 'C')) and (lastname < 'T'); But this is sort of ugly, and does not generalize well as the number of fields in the composite key increases. It is also not clear what this does to the query optimizer. Furthermore it may retrieve a bunch of records I have already seen, which I then have to filter out based on some other critera. Is there any elegant general solution to this problem? Thanks in advance

One option is to use an "offset" concept. In essence, the Query (canned into a Stored Procedure, possibly) would be re-executed (with the identical WHERE clause) for every page, but would "skip" the first "offset" rows. The value of the offset would be maintained by the Client application. For example, when the Client first asks for the list, the SP would be called with offset=0. The Client app. would loop through the foreach to fill the page (say 10). If the User now clicks on <next>, the SP would be called with an offset =10, causing it to loop thru the first 10 rows before returning anything to the Client. The next <next> would set offset =20 before calling the SP. <Previous> would reduce the offset. Important : These sort of queries *must* be supported by an index on the "order by" columns - you could get into serious sh*t if the Server is asked to sort a large number of rows with every click. Or, make sure that the number of rows that qualify is small enough. Even with the index, this is far from an ideal solution and must only be resorted to if you have absolutely no other option. Be absolutely sure that you cannot hold open a cursor - post your business problem if you like, someone may be able to help. Rudy flapdoodle wrote: > I have an application that needs to be able to deliver a list from a > database, a page at a time. > > For example, "all names starting with S." For various reasons, > the application cannot hold open a cursor while waiting for a user to > ask > for the next page, and a temp table is too expensive. Also, I can't > afford to keep the whole result set in memory (all names starting with > S, in a million person table, would be a big result set) and I don't > even want to fetch the whole result set unless the user needs it. > > So the question is... how do I do this? When the user hits "next", what > is the best approach? > > ...