---
name: "DBIx::Class::SQLMaker::LimitDialects"
section: "3pm"
platform: "linux"
package: "libdbix-class-perl"
version: "0.082844-1"
release: "Debian 13 (Trixie)"
platforms: ["linux"]
url: "https://new.carta.tech/DBIx::Class::SQLMaker::LimitDialects"
---

# DBIx::Class::SQLMaker::LimitDialects(3pm)

SQL::Abstract::Limit-like functionality in DBIx::Class::SQLMaker

## NAME

DBIx::Class::SQLMaker::LimitDialects - SQL::Abstract::Limit-like functionality in DBIx::Class::SQLMaker

## DESCRIPTION

DBIC's SQLMaker stack replicates and surpasses all of the functionality originally found in SQL::Abstract::Limit. While simple limits would work as-is, the more complex dialects that require e.g. subqueries could not be reliably implemented without taking full advantage of the metadata locked within DBIx::Class::ResultSource classes. After reimplementation of close to 80% of the SQL::Abstract::Limit functionality it was deemed more practical to simply make an independent DBIx::Class-specific limit-dialect provider.

## SQL LIMIT DIALECTS

Note that the actual implementations listed below never use `"*"` literally. Instead proper re-aliasing of selectors and order criteria is done, so that the limit dialect are safe to use on joined resultsets with clashing column names.

Currently the provided dialects are:

### 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.

## FURTHER QUESTIONS?

Check the list of additional DBIC resources.

## COPYRIGHT AND LICENSE

This module is free software copyright by the DBIx::Class (DBIC) authors. You can redistribute it and/or modify it under the same terms as the DBIx::Class library.