Sometimes you cannot or do not want to use an object-relational mapping tool. When not using an OR-mapper like Hibernate or Oracle Toplink you have to deal with database specifics. One common case especially for web applications is limiting the result set to a number of items that fit nicely on a web. You then often want to allow the users to navigate between these “pages” of items aka “paging”.
This type of functionality became part of SQL only as of SQL2008 in the following form:
SELECT * FROM t WHERE ... ORDER BY c OFFSET start_row FETCH count ONLY
Since most popular database management systems (DBMSes) do not yet implement this syntax you have to implement paging in propriatory ways.
My experience with an Oracle DBMS and the frustrating and comparatively long time it took to find the correct™ solution inspired me to write this post. Now I want to present you the syntax for some widely used DBMSes which we encounter frequently in our projects.
- MySQL, H2 and PostgreSQL (< 8.4 which will also implement the SQL2008 standard) use the same syntax:
SELECT * FROM t WHERE ... ORDER BY c LIMIT count OFFSET start - Oracle is where the fun begins. There is actually no easy and correct way of doing this. So you will end up with a mess like:
SELECT col1 FROM (SELECT col1, ROWNUM r FROM (SELECT col1 FROM table ORDER BY col1)) WHERE r BETWEEN start AND end
- DB2 AFAIK uses the syntax proposed in SQL2008 but correct me if I am wrong because we do not yet work with DB2 databases.
- As we did not need paging with MS SQLServer as of now I did not bother to look for a solution yet. Hints are very welcome.
With all solutions the ORDER BY clause is critical because SQL does not guarantee the order of the returned rows.
Wikipedia delivers some additional and special case information but does not really explain the general, real world case the specific DBMSes.
I hope that I raised some awareness about database specifics and perhaps saved you some time trying to find a solution the problem using your favorite DBMS.
Schneide Blog 
I disagree with you.
The way to paging with Oracle its how you describe using ROWNUMBER() and there is no “mess” because every RDBMS has his own way to do this.
In Sql Server 2005 you can use ROW_NUMBER() OVER (ORDER BY field ASC) similar to the oracle way.
Reggards, Emiliano.
@Emiliano: Thanks for your comment. Imho one of SQLs purposes is to abstract from the actual implementation of the DBMS. In reality there are differences between the different systems but I like the idea that you actually can migrate to another DBMS. In such a case you want to have and use a standard solution and not change your software because of the database.
My comment about the Oracle “mess” was partly because the solution has many pitfalls and is much more complex than the other implementations.
Best regards,
Miq
Miq: off course i would like too a tool (ORM or another tool if exists) that allow me to migrate easily to multiples DBMS, but i think that not exists such of thing. The only way we have its sometimes rewrite code and “hope” that work in every DMBS as posible.
Reggards, Emiliano.
SQL Server 2005/2008:
WITH NumberedItems AS (
SELECT ROW_NUMBER() OVER(ORDER BY i.Id ASC) AS Rowumber, i.*
FROM Items i
)
SELECT *
FROM NumberedItems
WHERE RowNumber BETWEEN @Start AND @End