The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer.
The LIMIT option does not impact how LIMIT is used when it is part of the WHERE Clause.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
The following retrieves rows 6-15:
SELECT * FROM table LIMIT 5,10
To retrieve all rows from a certain offset up to the end of the result set, you can use some large number for the second parameter. This statement retrieves all rows from the 96th row to the last:
SELECT * FROM table LIMIT 95,99999999
With one argument, the value specifies the number of rows to return from the beginning of the result set, which retrieves the first 5 rows:
SELECT * FROM table LIMIT 5
In other words, LIMIT n is equivalent to LIMIT 0,n.
SELECT ... LIMIT is processed similar to how the TOP option is processed. These following two commands are now equivalent:
SELECT TOP 5 ALL FROM SalesBonus ORDER BY NetAmount DESC
SELECT ALL FROM SalesBonus ORDER BY NetAmount DESC LIMIT 5
Examples:
Because of the 2 value LIMIT option, the following is supported, which has no "TOP" equivalent:
SELECT ALL FROM SalesBonus ORDER BY NetAmount DESC LIMIT 5,3
SELECT also supports the use of LIMIT with a WHERE clause. The following examples now work:
SELECT ALL FROM SalesBonus ORDER BY NetAmount DESC LIMIT 5 WHERE Bonus > 0
SELECT ALL FROM SalesBonus WHERE Bonus > 0 ORDER BY NetAmount DESC LIMIT 5
