SQL Limit: A Beginner's Guide (2024)

The SQL LIMIT statement restricts how many rows a query returns. A LIMIT statement appears at the end of a query, after any ORDER BY statements. You can start a LIMIT statement at a particular row using the offset argument.

When you’re working in SQL, you may only want to retrieve a specific number of rows from a query.

For example, you may want to retrieve the top three employees based on the number of “employee of the month” awards they have earned. This is easier to read than a list of all employees and how many “employee of the month” awards they have earned.

There is a built-in SQL function that allows you to perform this action: SQL LIMIT. Limit allows you to limit the number of records a query to a certain amount.

In this tutorial, we are going to discuss how to use the SQL LIMIT command. We’ll also discuss how to use TOP, the equivalent of LIMIT on SQL Server instances.

Query Refresher

To retrieve information from a database, you have to write SQL queries. Queries almost always begin with the SELECT statement. We use this statement to tell the database which columns we want to retrieve. Queries often include the FROM term, which is used to state which table the statement will get its data from.

Here is the syntax for an SQL query:

SELECT column_name FROM table_name WHERE conditions_apply;

Here is an example of an SQL query that will return a list of the names of all employees in the employees table:

SELECT name FROM employees;

Our query returns the following:

(8 rows)

What is SQL LIMIT?

The SQL LIMIT clause restricts how many rows are returned from a query. The syntax for the LIMIT clause is: SELECT * FROM table LIMIT X;. X represents how many records you want to retrieve.

For example, you can use the LIMIT clause to retrieve the top five players on a leaderboard. Or, you could use this clause the top five customers who have ordered the most products.

Here’s the syntax for a query that uses the LIMIT clause:

SELECT column_name FROM table_name LIMIT number_to_limit;

The LIMIT clause is only compatible with the SQL SELECT statement. You cannot use a LIMIT clause on an SQL UPDATE statement, for instance. Or you could not use a LIMIT clause on an SQL DROP or SQL ALTER TABLE statement.

Your LIMIT number must be positive. Say you want to retrieve records from the bottom of the list. You should use the SQL ORDER BY statement to order them in descending order. Then, you should use a LIMIT statement:

SELECT * FROM table ORDER BY column DESC LIMIT X;

This is the syntax for ordering a table in descending order and limiting the output of the query. We’ve used an ORDER BY clause to retrieve only the first X records.

SQL LIMIT Rows Example

Let’s use an example to illustrate the SQL LIMIT clause in action. Say that we want to get a list of the names of the top three longest-serving employees within our organization.

We also want to retrieve the dates they were hired. We could use the following query to retrieve that information:

SELECT name, hired_date FROM employees ORDER BY hired_date DESC LIMIT 3;

Our query returns the following:

(3 rows)

Our query has returned the top three longest-serving employees by the date on which they were hired.

Offset the SQL LIMIT Clause

If you want to offset the LIMIT clause—change the starting point—you can specify a second parameter. Here is the syntax for an SQL LIMIT query that offsets a query:

SELECT column_name FROM table_name LIMIT starting_point, rows_to_return;

The starting point is the offset for the query (where your results will start). The “rows_to_return” is how many rows you want your query to retrieve.

Let’s say that we want to get the name of the fifth-highest paid employee. We could use the LIMIT clause to specify that we want to get only one record, which should be the fifth highest-paid employee. Here’s an example of such a query:

SELECT name FROM employees ORDER BY salary DESC LIMIT 4, 1;

We specify that we want to get the item which comes fifth in our list—remember, the first row is. We only want to get one record. Here’s the result of our query:

(1 row)

The offset clause only works in MySQL. If you want to run an offset operation in PostgreSQL, you’ll need to use the OFFSET operator like so:

SELECT name FROM employees ORDER BY salary DESC OFFSET 4 LIMIT 1;

SQL TOP

In SQL Server and MS Access the SQL TOP clause is used to limit the number of rows returned by a query. Here is the syntax for an SQL query that uses TOP:

SELECT TOP number column_names FROM table_name;

Here’s an example of a query that will return the top two highest-paid employees from the employees table:

SELECT TOP 2 * FROM employees ORDER BY salary DESC;

Our query returns the following:

(2 rows)

The TOP clause can be followed by the PERCENT keyword, which retrieves the percentage of rows instead of the number of fixed rows. So, if you want to get the top 25 percent of employees based on their salaries, you could use this query:

SELECT TOP 25 PERCENT * FROM employeesORDER BY salary DESC;

Our query on the database with eight records returns the following:

(2 rows)

Conclusion

You can use the SQL LIMIT operator (or TOP in SQL Server and MS Access) to limit the number of rows a query returns. This is useful if you only need to see the top or bottom entries from a query.

For example, you may want to get the records of the five employees with the highest salaries. In this tutorial, we discussed how queries are structured in SQL, and how to use the LIMIT operator.

Do you want to learn more about SQL? Read our complete How to Learn SQL guide for expert advice and tips, and for guidance on online courses.