Workaround to Avoid Auto Sorting When Using Query Group By Clause in Sheets

The Group By clause in Query sorts the output automatically. It’s ideal in most of the cases. But to avoid auto-sorting of data when using the Query Group By clause, there is no built-in clause in Query. That prompts me to think about a workaround!

I am going to address the below two points in this Query related tutorial.

  1. How to use the Group By clause in Google Sheets Query.
  2. How to avoid auto sorting in Query Group By clause.

Screenshot # 1:

Avoid auto-sorting of data when using the Query Group By clause

When you use the Group By clause in Query to make the summary of data in B2:C8, it will return the output as per the values in the array/range H2:I5. As you can see, the items (fruits) are sorted alphabetically.

I am not telling you that this’s an issue to solve. But by any reason, if you want to return the output that without sorting the items (fruits) then you can use my workaround.

With my workaround, you can avoid auto sorting when using Query Group By clause in Google Sheets. The Query output in the range E2:F5, demonstrates the same using my workaround formula.

How to do that? Before proceeding to the workaround first see how to use the Group By clause in Query function in Google Sheets.

How to Use the Query Group By Clause in Google Sheets

To learn how to avoid auto sorting in Query Group By clause, you must know the proper use of this clause and about its auto-sorting.

Must Check: Google Sheets Functions Guide.

The Group By clause in Query is used to aggregate values across rows. A single row is created for each distinct combination of values (groups) in the Group By clause.

As mentioned above the grouped data will be automatically sorted by the grouping columns.

Example 1: Single Column Grouping

=Query(A1:C7,"Select A, Sum(C) group by A")

Screenshot # 2:

Single column grouping - Query

Example 2: Two Column Grouping

=Query(A1:C7,"Select A,B, Sum(C) group by A,B")

Screenshot # 3:

Two column grouping - Query

In the above examples, I have used the Sum aggregation function in Query. You can use other aggregation functions such as Max, Min, Avg, Count with the Group By clause.

Must Check: How to Sum, Avg, Count, Max, and Min in Google Sheets Query.

When you use the Query Group By clause as above, use only the columns in the Select clause in it.

How to Avoid Auto Sorting When Using Query Group By Clause

I think this time I must use some real-life data. Let’s import a table from this source. The table contains the details of the FIFA World Cup finals.

To import the required data, we can use the IMPORTHTML function. Open a new Google Sheets file. In that enter the below formula in cell A1.

=IMPORTHTML("https://en.wikipedia.org/wiki/List_of_FIFA_World_Cup_finals","table",3)

At present, the above formula returns the below table. In any case, if you are unable to import the data, then just type first few rows manually. That will be sufficient for our test.

Screenshot # 4:

Avoid auto sorting in Query Group By clause - FIFA Finals Sample Data

In this data what we want to concentrate is on the second column. The first column contains the year of the matches and the second column the names of the winning nation.

Here I am going to use a Query formula. With the help of the Group By clause, you can count the values (country names) in column B. That will show the number of titles won by each nation.

=query(B1:B22,"Select B, count(B) where B is not null group by B",1)

Screenshot # 5:

Example shows the Query group by output with default sorting

In this summary, you can see the winners and count, I mean the result by the nation. We have not used the Order By clause in Query to sort the data. The Group By clause automatically sorted the country names alphabetically.

Here comes the importance of my workaround and see that result first.

Screenshot # 6:

Example shows the Query group by output with no sorting

In this example, I have applied my workaround to avoid auto sorting in the Query Group By clause. So the output keeps the original order of the source data.

In this particular source data (screenshot # 4), column A contains years of the FIFA World Cup final matches.

Keeping the original order of column B in the summary (screenshot # 6) helps us to retain the winners (country) name by chronological order.

Workaround to Retain the Source Data Order in The Query Group By Clause

Here is the step by step instructions. Actually, the workaround to avoid auto sorting when using Query Group By clause involves four main functions – ROW, SORT, SORTN, and INDEX.

We want to group the range B2:B28 using Query (please refer to screenshot # 4).

Step # 1: Add a Sequential Number Column with the Grouping Column

This is the key. The below formula will add sequential numbers (2, 3, 4…) with the data in the range B2:B22.

=ArrayFormula({B2:B22,row(B2:B22)})

Screenshot # 7:

Sequential number column with the Grouping column

Under Step # 3, I’ve explained why we require this sequential number column.

Step # 2: Sorting of Grouping Column and Sequential Number Column

We must sort the column 1 in the above output in ascending order then by column 2 that also in ascending order.

So I am going to use the function SORT here. When using SORT, we can remove the ArrayFormula. See the generic formula first.

SORT({range}, sort_column, is_ascending, sort_column2,is_ascending2)

Formula in line with the above generic formula.

=sort({B2:B22,row(B2:B22)},1,1,2,1)

Step # 3: SORTN to Create a Single Row for Each Distinct Combination of Values

Now it’s time to use my one of the favorite function after Query. That’s none other than the SORTN.

You can use SORTN to remove the duplicates in column 1. In other words, similar to Query grouping, it creates a single row for each distinct combination of values.

The SORTN contains display tie mode numbers and the number # 2 in that is exclusively for removing duplicates.

SORTN(range, [n], [display_ties_mode], [sort_column], [is_ascending])

The SORT formula under Step # 2 is the range in the SORTN. Here is that SORTN formula.

=sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1)

[n] – the number of rows to return after removing the duplicates. We want the total rows after removing the duplicate rows. But we don’t know the exact number. So I’ve used 9^9 to represent “ALL”.

If you apply the below formula you can understand it.

=9^9

Result: 387420489

2 – This after the [n] is the tie mode number to delete duplicates.

1 – The column to consider duplicates. It’s the column 1 that contain the country name.

1 – Ascending order. This sort order is very important. The Query group by clause sorts the grouping column in ascending order. So in SORTN also we must sort this column accordingly.

The SORTN formula returns the country names after removing duplicates in ascending order. It also contains the serial numbers as the second column.

See the screenshot in Step # 4 below. This serial number I will use later to Sort the Query output. That’s why I have told you earlier, it’s the key.

Step # 4: Extract the Sequential Number Column

Enter the below Query formula in cell J1 and the just above SORTN formula in cell M2 and compare the country names in each output. You can see that both are in the same order and unique.

=query(B1:B22,"Select B, count(B) where B is not null group by B",1)

Screenshot # 8:

Single row for each distinct combination using Query/SORTN

We only want the second column in the SORTN output. Using Index function we can offset the first column.

={"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}

Additionally I have added a label “Sr” on the top (refer cell L1 on the screenshot # 9 below).

Step # 5: Prepare the Data to Avoid Auto Sorting When Using Query Group By Clause

Combine the Query formula and the above Index combo using Curly Brackets.

={query(B1:B22,"Select B, count(B) where B is not null group by B",1),{"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}}

Screenshot # 9:

The data to avoid auto sorting when using Query Group By clause

Step # 6: Final Step

In this final step, use the above three columns as the data in another Query and use the Order By clause to sort the third column in ascending order. Then extract column 1 and 2 only.

=Query({query(B1:B22,"Select B, count(B) where B is not null group by B",1),{"sr";index(sortn(sort({B2:B22,row(B2:B22)},1,1,2,1),9^9,2,1,1),0,2)}},"Select Col1,Col2 order by Col3 Asc")

That’s all. This is the workaround to avoid auto sorting in Query Group By clause in Sheets.

Can I do the same with two column data that contain one grouping column and one value column?

Yes! The formula is almost the same.

=Query({query(B2:C8,"Select B, sum(C) group by B label sum(C)''"),index(sortn(sort({B2:B8,row(B2:B8)},1,1,2,1),9^9,2,1,1),0,2)},"Select Col1, Col2 order by Col3 asc")
Two column - grouping without sorting

Thanks for the stay! Enjoy!

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Hierarchical Numbering Sequences in Excel

Creating hierarchical numbering sequences in an Excel spreadsheet can significantly improve the way you...

How to Easily Repeat a Sequence of Numbers in Excel

Excel offers multiple ways to accomplish tasks, and the simplicity of each approach depends...

Create a Sequence of Dates at Every Nth Row in Excel (Dynamic Array)

Would you like to create a sequence of dates in every nth row in...

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

More like this

XMATCH Row by Row: Finding Values Across a Range in Google Sheets

Using the BYROW function with XMATCH in Google Sheets allows us to match values...

Limit Formula Expansion to a Specific Row in Google Sheets

In this tutorial, I’ll explain how to limit the expansion of an array formula...

3-D Referencing Structured Data Tables in Google Sheets

When you have several tables within a single sheet—not across multiple sheets in a...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.