Because of the type of application, I can't use the standard "next 10 items" type of paging. In many applications, you can start at offset 10 for the second page. My situation is a bit different. I'm using Hibernate3 and various databases (Oracle9, MySQL 4.1, MS SQL server).

First, I need to page based on a time interval (say 1 day). I can accomplish this using "between" with the date range. Second, I need to know when there is older and newer data, to show the "next" and "previous" links. Currently I'm doing this by doing a second select for the "MIN(paging_field), MAX(paging_field)". Third, in most situations I want a "sliding window", so that page 2 will contain 24 hours worth of items, regardless of where the next item lies relative to the end of the first page. This prevents seeing a blank page with previous and next links. When the sliding window is used, I do a third query for the "MIN(paging_field) where paging_field > window_start".

This all works great for me. It doesn't fetch the entire result set, since there are instances with large result sets. It works flawlessly in the face of changing data. The paging_field has a key on it. It's all pretty fast with the number of hits we have (kind of a pain to code though). My question is this: without using stored procedures (I don't have access to add them to this database), is there a better way to accomplish this with fewer than 3 queries? (1: global min/max 2: local min 3: fetch data), or should I stick with this strategy? Thanks in advance.