The LIMIT clause can be used to limit the number of records displayed. It takes one or two arguments. You can specify a very big integer for row_count to output to the last row, starting from a specific row.
The LIMIT clause can be used as a prepared statement. In this case, the bind parameter (?) can be used instead of an argument.
INST_NUM() and ROWNUM cannot be included in the WHERE clause in a query that contains the LIMIT clause. Also, LIMIT cannot be used together with FOR ORDERBY_NUM() or HAVING GROUPBY_NUM().
LIMIT [offset,] row_count
--LIMIT clause can be used in prepared statement
PREPARE STMT FROM 'SELECT * FROM sales_tbl LIMIT ?, ?';
EXECUTE STMT USING 0, 10;
--selecting rows with LIMIT clause
SELECT * FROM sales_tbl WHERE sales_amount > 100 LIMIT 5;
dept_no name sales_month sales_amount
==============================================================
201 'George' 1 450
201 'Laura' 2 500
301 'Max' 4 300
501 'Chang' 5 150
501 'Sue' 6 150
--LIMIT clause can be used in subquery
SELECT t1.* FROM
(SELECT * FROM sales_tbl AS t2 WHERE sales_amount > 100 LIMIT 5) AS t1 LIMIT 1,3;
dept_no name sales_month sales_amount
==============================================================
201 'Laura' 2 500
301 'Max' 4 300
501 'Chang' 5 150