We can use various options to limit the number of rows in the processed data in the Google Sheets QUERY function.

There are two QUERY language clauses – OFFSET and LIMIT. Additionally, we can encapsulate the formula with either ARRAY_CONSTRAIN or the CHOOSEROWS function.

The LIMIT clause, ARRAY_CONSTRAIN, and CHOOSEROWS functions are used to limit the number of rows in the aggregated data. The OFFSET function, on the other hand, is employed to offset a certain number of rows from the beginning of the processed data.

Sample Data

The sample data comprises items, salesperson, quantity, and amount in the range A1:D.

There are multiple occurrences of two items. We will group the items, sum the amounts, and sort the results.

Let’s limit the number of rows to 1 using QUERY or two other functions.

LIMIT Clause to Limit the Number of Rows in QUERY

We will explore the use of the LIMIT clause to restrict the number of rows in two steps. Let’s begin with understanding the syntax.

Syntax of the QUERY function: QUERY(data, query, [headers])

Step 1 Formula:

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A LIMIT 1", 1)

Result:

Where:

• data: A1:D
• query: "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A LIMIT 1"
  • SELECT A, SUM(D): Select column A and calculate the sum of values in column D.
  • WHERE A IS NOT NULL: Filters out any rows where column A is empty.
  • GROUP BY A: Groups the results by values in column A.
  • LIMIT 1: Limits the result to just one row.
• headers: 1 (The data has a header row.)

Step 2: Sorting the aggregated values and limiting the rows:

When you don’t specify the ORDER BY clause, the output of the query formula will be sorted by the columns in the GROUP BY clause.

To limit the number of rows after sorting, use the ORDER BY clause. Here is an example:

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(D) ASC LIMIT 1", 1)

Result:

This formula will sort the grouped results based on the sum of values in column D in ascending order.

If you’re unfamiliar with QUERY language clauses, please refer to this guide: What is the Correct Clause Order in Google Sheets Query?

Offsetting Rows in Google Sheets QUERY

Offsetting rows is another way of limiting the number of rows in Google Sheets QUERY.

In some scenarios, you might need to skip a certain number of rows from the beginning of the QUERY results. This is where the OFFSET clause in QUERY becomes relevant.

In the following formula, we use the LIMIT clause to restrict the rows to 2 and OFFSET to skip 1 row. So even though the number of rows limited is 2, the output will contain only 1 row due to the offset.

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A ORDER BY SUM(D) ASC LIMIT 2 OFFSET 1", 1)

Limiting Number of Rows in QUERY Using CHOOSEROWS or ARRAY_CONSTRAIN

You can restrict the number of rows without using the LIMIT clause in QUERY.

Here’s how to utilize ARRAY_CONSTRAIN as well as CHOOSEROWS with QUERY for this purpose:

Base QUERY Formula:

=QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A", 1)

Using ARRAY_CONSTRAIN:

Syntax: ARRAY_CONSTRAIN(input_range, num_rows, num_cols)

Formula:

=ARRAY_CONSTRAIN(QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A", 1), 2, 2)

Where:

• input_range is the QUERY formula,
• num_rows is 2 (including the header row), and
• num_cols is 2.

Using CHOOSEROWS:

Syntax: CHOOSEROWS(array, [row_num1, …])

Formula:

=CHOOSEROWS(QUERY(A1:D, "SELECT A, SUM(D) WHERE A IS NOT NULL GROUP BY A", 1), SEQUENCE(2))

Where:

• array is the QUERY formula, and
• row_num1 is SEQUENCE(2). Within SEQUENCE, specify the number of rows to limit including the header row.

This approach has a slight advantage over others. You can use SEQUENCE to dynamically control the extraction of rows:

For example, if you want to extract the last 5 rows, use the following SEQUENCE:

=SEQUENCE(5, 1, -1, -1)

(Our sample data is very small and doesn’t contain 5 rows. So you can test it with a larger dataset.)

Resources

Here are some interesting tutorials to read about QUERY.

• How to Use And, Or, and Not in Google Sheets Query
• How to Use Cell Reference in Google Sheets Query
• CONTAINS Substring Match in Google Sheets Query for Partial Match
• Multiple CONTAINS in WHERE Clause in Google Sheets Query
• How to Use Date Criteria in Query Function in Google Sheets [Date in Where Clause]