LIMIT and OFFSET to Cap Results

1. LIMIT Clause

The LIMIT clause helps to constrain the number of rows that are returned by a query. It is an optional clause added to the end of the SELECT statement.

SELECT <columns>
FROM <tablename>
LIMIT <rowcount>;

For example, let’s take a look at invoices by their total values in descending order:

SELECT *
FROM invoice
ORDER BY total DESC;

This ends up returning all of the rows (412 in this case), although in a larger database, this can take a long time to return all rows:

If we were only interested in looking at the top five, we could add the LIMIT clause so that the query only returns the top five rows:

SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5;

You will see the same top five rows as the prior query, but it returns much faster. Exploring data in small sets can be more efficient. Think about how you search results on a search engine. The results do not return every single result on the same page; it would simply be too slow to do so. Instead, they return only the top few results.

Let’s look at another example of invoices that have the billing_country as Belgium in descending order:

SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC;

This should return seven rows:

If we had set the LIMIT to 10:

SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10;

The result set would stay the same, with seven rows:

2. OFFSET Clause

If we want to skip a number of rows when returning results, we can use the OFFSET clause. The OFFSET clause is placed after the LIMIT clause.

SELECT <columns>
FROM <tablename>
LIMIT <rowcount>
OFFSET <rowstoskip>;

By doing this, the statement will skip the first number of rows in the OFFSET and then return the number of rows based on the LIMIT. A search engine with multiple pages of content works in a very similar way.

Using our invoice example, perhaps we want to look through each set of invoices five lines at a time. To look at the next five rows, we would run the query using:

SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5
OFFSET 5;

If we wanted to look at the next five rows after that:

SELECT *
FROM invoice
ORDER BY total DESC
LIMIT 5
OFFSET 10;

Using the OFFSET and LIMIT clauses, we can jump to any subset of rows being returned within the SELECT statement.

Looking at our prior example of invoices in Belgium:

SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10;

Recall that the result set returned seven rows. But look at what happens if we add the OFFSET to 5.

SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10
OFFSET 5;

Again, this would skip the first five rows and return the number of rows remaining, up to the LIMIT of 10:

If we had added an OFFSET of 10, we would exceed the number of rows available to return:

SELECT *
FROM invoice
WHERE billing_country = 'Belgium'
ORDER BY total DESC
LIMIT 10
OFFSET 10;

So the query would simply return 0 rows:

try it

Your turn! Open the SQL tool by clicking on the LAUNCH DATABASE button below. Then enter in one of the examples above and see how it works. Next, try your own choices for which columns you want the query to provide.