Don’t forget an "order by" for pagination queries

I’ve seen this many times in web-based applications that use paginated results.

If you’re using Oracle, the general form of such a query is :

   select * from
   (
      select /*+ FIRST_ROWS(n) */ a.*, ROWNUM rnum from
          (your_query_goes_here) a
      where  ROWNUM <=:MAX_ROW_TO_FETCH
   )
  where rnum  >= :MIN_ROW_TO_FETCH;

While doing this with JDBC/SQL is easy enough-if you’re using Hibernate HQL (with the appropriate Oracle Dialect), you can also take advantage of this feature. For example:

Query q = "from Customer c order by c.lastName";
q.setFirstResult(10);
q.setMaxResults(30);
List results = q.list();

For this query to work correctly, all the time, it is important to always sort by a unique column. In the example above, “lastName” will likely have duplicate values. There is no way to guarantee the order of the returned rows unless we include a unique ‘order by’ clause. If a unique column is not available, in many cases, you can just sort by ROWID. See this article here for more on rownum.The modified HQL would look like

"from Customer c
order by c.lastName, c.rowId"

Of course, you would have to map a java property “rowId” to the pseudo-column “ROWID”.
While mapping it in hibernate, be sure to mark it as update=”false” and insert=”false”, since you cannot update the ROWID column.