How to Use the SQL Limit and Offset Clause in Google Sheets

‍

The source sheet or database may contain too many entries that can overwhelm your spreadsheet. You can use the LIMIT function to limit the number of entries that are copied to your spreadsheet. 

In this tutorial, we learn methods of limiting the output of our query using the LIMIT function and an additional technique in limiting the output through a certain criteria. Our source sheet is the following:

Limit to first N entries 

To apply the LIMIT clause, use the following query:

select * limit 10

Where the asterisk after the select clause indicates that all columns are selected, while the number 10 after the limit command limits the results to 10 rows. The whole formula is as follows:

=query(week27!A2:H633, "select * limit 10")

The resulting output is the following:

Limit to first N entries, arranged in order

You can combine the limit clause with order by clause to get the entries with smallest or biggest values of a certain attribute, stored in a specific column. For our example, we want to list the first ten of the orders with the smallest profit. The following query can be used:

select * order by H limit 10

Where the profits are stored in column H. The whole formula is as follows:

=query(week27!A2:H633, "select * order by H limit 10")

The resulting output is the following:

If you want to list the first ten of the orders with the greatest profit, use the DESC clause. The following query can be used:

select * order by H desc limit 10

Where the profits are stored in column H. The whole formula is as follows:

=query(week27!A2:H633, "select * order by H desc limit 10")

The resulting output is the following:

Limit to entries with certain entry value

Finally, you may want to limit the output to entries with a certain value. For example, let’s say you just want to get the first ten orders of grain bread. For that, we will combine LIMIT with CONTAIN. The query will be as follows:

select * where B contains 'grain bread' limit 10

Where B refers to the 2nd column where the item is listed. The full formula is as follows:

=query(week27!A2:H633, "select * where B contains 'grain bread' limit 10")

And the result is:

Limit to entries from certain date

Oftentimes the source sheet will contain a column that contains date and/or time. To limit the selection to a specific date, add the DATE clause besides LIMIT and CONTAIN. For our example, we wish to list the first ten entries during June 30. The specific query will be as follows:

select * where A contains date '2020-06-30' limit 10

The full formula is as follows:

=query(week27!A2:H633, "select * where A contains date '2020-06-30' limit 10")

And the result is:

The trick is to add the DATE clause so that Google Sheets can process the date using the proper date format instead of a string. 

Skip the first entries by using Offset

Finally, you can skip the first entries by using OFFSET. For our first example, we will solely use OFFSET:

select * offset 50

The whole formula is as follows:

=query(week27!A2:H58, "select * offset 50")

We have intentionally reduced the range in order to show that the OFFSET clause works. The result is:

You can combine OFFSET with LIMIT. For our example, we will use the following query:

select * limit 10 offset 50

The order is important: if you added OFFSET before LIMIT, the query will not work. The whole formula is as follows:

=query(week27!A2:H633, "select * limit 10 offset 50")

And the result is:

Sample Sheet

One understands best how something works by trying it. Here is a sample sheet that you can use to check how the LIMIT clause works in Google Sheets.

SQL Limit Sample Sheet