MySQL Limit Query - How to Limit Query Results
Sometimes it is useful to limit the number of rows that are returned from an SQL query. For example, if a user knows exactly which rows of a table they are interested in, they can specify which of those rows to return via the MySQL limit syntax. This can be especially useful when querying very large tables.
The MySQL limit syntax can either return the first so many rows from a database table, or it can also return a range of rows from the database table. Listed below are some examples.
Example 1: Returning the first 100 rows from a table called employee:
SELECT
*
FROM
employee
LIMIT 100
Example 2: Returning a range of rows from a table called employee (starting 2 rows past the first record, return the next 4 rows). In this example, 2 is the OFFSET, and 4 is the number of rows to return:
SELECT
*
FROM
employee
LIMIT 2,4
The above query can also be written in the following way using the LIMIT / OFFSET syntax for better readability:
SELECT
*
FROM
employee
LIMIT 4 OFFSET 2
Example 3: Returning the first 100 rows from a table named employee using an ORDER BY clause:
SELECT
*
FROM
employee
ORDER BY
id
LIMIT 100
Many other databases also support limiting rows returned from queries. Listed below are links that show how to limit rows for other popular databases:
- Cassandra Limit Rows
- DB2 Limit Rows
- Derby Limit Rows
- Firebird Limit Rows
- H2 Limit Rows
- HSQLDB Limit Rows
- Informix Limit Rows
- Microsoft SQL Server Limit Rows
- Oracle Limit Rows
- Pervasive Limit Rows
- PostgreSQL Limit Rows
- Redshift Limit Rows
- Salesforce Limit Rows
- SimpleDB Limit Rows
- SQLite Limit Rows
- Sybase Limit Rows
- Teradata Limit Rows
