LimitOffset¶

 SELECT ... LIMIT $limit OFFSET $offset

Supported by PostgreSQL and SQLite

LimitXY¶

 SELECT ... LIMIT $offset, $limit

Supported by MySQL and any SQL::Statement based DBD

RowNumberOver¶

 SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER( ORDER BY ... ) AS RNO__ROW__INDEX FROM (
   SELECT ...
  )
 ) WHERE RNO__ROW__INDEX BETWEEN ($offset+1) AND ($limit+$offset)

ANSI standard Limit/Offset implementation. Supported by DB2 and MSSQL >= 2005.

SkipFirst¶

 SELECT SKIP $offset FIRST $limit * FROM ...

Supported by Informix, almost like LimitOffset. According to SQL::Abstract::Limit "... SKIP $offset LIMIT $limit ..." is also supported.

FirstSkip¶

 SELECT FIRST $limit SKIP $offset * FROM ...

Supported by Firebird/Interbase, reverse of SkipFirst. According to SQL::Abstract::Limit "... ROWS $limit TO $offset ..." is also supported.

RowNum¶

Depending on the resultset attributes one of:

 SELECT * FROM (
  SELECT *, ROWNUM AS rownum__index FROM (
   SELECT ...
  ) WHERE ROWNUM <= ($limit+$offset)
 ) WHERE rownum__index >= ($offset+1)

or

 SELECT * FROM (
  SELECT *, ROWNUM AS rownum__index FROM (
    SELECT ...
  )
 ) WHERE rownum__index BETWEEN ($offset+1) AND ($limit+$offset)

or

 SELECT * FROM (
    SELECT ...
  ) WHERE ROWNUM <= ($limit+1)

Supported by Oracle.

Top¶

 SELECT * FROM
 SELECT TOP $limit FROM (
  SELECT TOP $limit FROM (
   SELECT TOP ($limit+$offset) ...
  ) ORDER BY $reversed_original_order
 ) ORDER BY $original_order

Unreliable Top-based implementation, supported by MSSQL < 2005.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

FetchFirst¶

 SELECT * FROM
 (
 SELECT * FROM (
  SELECT * FROM (
   SELECT * FROM ...
  ) ORDER BY $reversed_original_order
    FETCH FIRST $limit ROWS ONLY
 ) ORDER BY $original_order
   FETCH FIRST $limit ROWS ONLY
 )

Unreliable FetchFirst-based implementation, supported by IBM DB2 <= V5R3.

CAVEAT

Due to its implementation, this limit dialect returns incorrect results when $limit+$offset > total amount of rows in the resultset.

GenericSubQ¶

 SELECT * FROM (
  SELECT ...
 )
 WHERE (
  SELECT COUNT(*) FROM $original_table cnt WHERE cnt.id < $original_table.id
 ) BETWEEN $offset AND ($offset+$rows-1)

This is the most evil limit "dialect" (more of a hack) for really stupid databases. It works by ordering the set by some unique column, and calculating the amount of rows that have a less-er value (thus emulating a "RowNum"-like index). Of course this implies the set can only be ordered by a single unique column.

Also note that this technique can be and often is excruciatingly slow. You may have much better luck using "software_limit" in DBIx::Class::ResultSet instead.

Currently used by Sybase ASE, due to lack of any other option.