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;