Row limiting clause (SELECT)

How to use the right clause to limit the number of rows produced by a SELECT statement?

IBM® Informix® SQL supports the SKIP and FIRST/LIMIT keywords to limit the number of rows of a result set.

For example:

SELECT SKIP 10 FIRST 20 customer.* FROM customer ... ORDER BY cust_name

Note: It is strongly recommended to use an ORDER BY clause when limiting the result set rows.

The above Informix SQL syntax is not portable.

Recent database engines support the row limiting clause syntax defined by the SQL standard:

SELECT ... OFFSET n ROWS FETCH FIRST m ROWS ONLY

This should be the prefered syntax to be used, if all target database types support this SELECT clause.

The ODI database drivers can convert the Informix SQL SKIP/FIRST row limiting clause to a native SQL equivalent, if the row limiting clause parameters are simple integer literals (the clause is not translated when using SQL parameters / program variables).

Important: In addition to the SKIP/FIRST clause of the projection clause, Informix SQL supports also a LIMIT clause after the ORDER BY clause:

SELECT customer.* FROM customer ... ORDER BY cust_name LIMIT 10

This Informix SQL syntax construction is not converted by the ODI drivers. To benefit from the conversion, review the code to use the Informix SQL SKIP/FIRST clause instead.

Table 1. Informix SELECT [SKIP n] FIRST m syntax by database brand
Database Server Type	Native equivalent	Informix emulation
IBM DB2® LUW 11.1+	`SELECT ... ORDER BY ... [OFFSET n ROWS] FETCH FIRST m ROWS ONLY`	Emulated, see details
IBM Informix 12+	`SELECT [SKIP n] FIRST m ...`	Yes, native SQL feature
IBM Netezza 7+	`SELECT ... ORDER BY ... LIMIT m [OFFSET n]`	Emulated, see details
Microsoft™ SQL Server 2012+	`SELECT ... ORDER BY ... OFFSET n ROWS [ FETCH FIRST m ROWS ONLY ]`	Emulated, see details
Oracle® MySQL 5.5-8.0 / MariadDB 10.x	`SELECT ... ORDER BY ... LIMIT m [OFFSET n]`	Emulated, see details
Oracle Database Server 12c+	`SELECT ... ORDER BY ... [OFFSET n ROWS] FETCH FIRST m ROWS ONLY`	Emulated, see details
PostgreSQL 9.4+	`SELECT ... ORDER BY ... [OFFSET n ROWS] FETCH FIRST m ROWS ONLY`	Emulated, see details
SAP® ASE 16	`SELECT TOP(m) ...`	Not supported, see details
SAP HANA® 2.0+	`SELECT ... ORDER BY ... LIMIT m [OFFSET n]`	Emulated, see details
SQLite 3.8+	`SELECT ... ORDER BY ... LIMIT m [OFFSET n]`	Emulated, see details