There are a method to paging using ANSI SQL only?

No official way, no.*

Generally you'll want to have an abstracted-out function in your database access layer that will cope with it for you; give it a hint that you're on MySQL or PostgreSQL and it can add a 'LIMIT' clause to your query, or rownum over a subquery for Oracle and so on. If it doesn't know it can do any of those, fall back to fetching the lot and returning only a slice of the full list.

*: eta: there is now, in ANSI SQL:2003. But it's not globally supported, it often performs badly, and it's a bit of a pain because you have to move/copy your ORDER into a new place in the statement, which makes it harder to wrap automatically:

SELECT * FROM (
    SELECT thiscol, thatcol, ROW_NUMBER() OVER (ORDER BY mtime DESC, id) AS rownumber
)
WHERE rownumber BETWEEN 10 AND 20 -- care, 1-based index
ORDER BY rownumber;

There is also the "FETCH FIRST n ROWS ONLY" suffix in SQL:2008 (and DB2, where it originated). But like the TOP prefix in SQL Server, and the similar syntax in Informix, you can't specify a start point, so you still have to fetch and throw away some rows.


See Limit—with offset section on this page: http://troels.arvin.dk/db/rdbms/

BTW, Firebird also supports ROWS clause since version 2.0


In nowadays there is a standard, not necessarily a ANSI standard (people gave many anwsers, I think this is the less verbose one)

SELECT * FROM t1 
WHERE ID > :lastId
ORDER BY ID
FETCH FIRST 3 ROWS ONLY

It's not supported by all databases though, bellow a list of all databases that have support

  • MariaDB: Supported since 5.1 (usually, limit/offset is used)
  • MySQL: Supported since 3.19.3 (usually, limit/offset is used)
  • PostgreSQL: Supported since PostgreSQL 8.4 (usually, limit/offset is used)
  • SQLite: Supported since version 2.1.0
  • Db2 LUW: Supported since version 7
  • Oracle: Supported since version 12c (uses subselects with the row_num function)
  • Microsoft SQL Server: Supported since 2012 (traditionally, top-N is used)

You can use the offset style of course, although you could have performance issues

SELECT * FROM t1
ORDER BY ID
OFFSET 0 ROWS
FETCH FIRST 3 ROWS ONLY

It has a different support

  • MariaDB: Supported since 5.1
  • MySQL: Supported since 4.0.6
  • PostgreSQL: Supported since PostgreSQL 6.5
  • SQLite: Supported since version 2.1.0
  • Db2 LUW: Supported since version 11.1
  • Oracle: Supported since version 12c
  • Microsoft SQL Server: Supported since 2012

Tags:

Sql

Ansi Sql

Recent Posts