Limit in SQL
In SQL, the LIMIT
clause is used to specify the maximum number of rows that a SELECT statement should return. For example:
SELECT * FROM users LIMIT 10;
This SQL statement would return the first 10 rows from the users
table.
You can also use the LIMIT
clause in combination with the OFFSET
clause to skip a certain number of rows before starting to return rows. For example:
SELECT * FROM users LIMIT 10 OFFSET 20;
This SQL statement would return rows 21 through 30 of the users
table.
Note that the order of the LIMIT
and OFFSET
clauses can vary depending on the database you are using. Some databases, such as MySQL, allow you to use the LIMIT
clause before the OFFSET
clause, while others, such as PostgreSQL, require the OFFSET
clause to be specified before the LIMIT
clause.
Limit example in various Databases
Here are some examples of how you can use the LIMIT
clause in various databases:
- MySQL:
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
- PostgreSQL:
SELECT * FROM users LIMIT 10;
SELECT * FROM users LIMIT 10 OFFSET 20;
- Oracle:
SELECT * FROM (SELECT * FROM users) WHERE ROWNUM <= 10;
SELECT * FROM (SELECT * FROM users WHERE ROWNUM > 20) WHERE ROWNUM <= 10;
- Microsoft SQL Server:
SELECT TOP 10 * FROM users;
SELECT * FROM (SELECT *, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS row FROM users) AS a WHERE row > 20 AND row <= 30;
Things you might want to know about SQL Limit
- To return the top 10 rows from a table, sorted by a specific column in descending order:
SELECT * FROM users ORDER BY score DESC LIMIT 10;
- To return the next 10 rows after the first 20 rows, sorted by a specific column in ascending order:
SELECT * FROM users ORDER BY score ASC LIMIT 10 OFFSET 20;
- To return the top 10 rows from a table, sorted by a specific column in ascending order, and select only certain columns:
SELECT username, score FROM users ORDER BY score ASC LIMIT 10;