3

Google Spreadsheet's Query function returns empty data with order by. Here is the Query that is working properly:

=QUERY('OtherListName'!A1:C;"Select A, B, C";1)

This query returns exactly what you'd expect: the contents of the range A1:C (which has 6 lines in my case). Then I try to order the returned data:

=QUERY('OtherListName'!A1:C;"Select A, B, C order by A, B";1)

This query only return the first line (that contains headers), and nothing more. The original set of data in the OtherListName contains only strings and integers.

What I want is to get the data ordered by column A and then by column B. Both columns only contain strings. The corresponding integers are in the column C.

Please share some advice on this, I haven't found anything yet. Meanwhile I'll continue with my experiments to find out the reason of this "error". Thanks.

tehhowch
  • 9,645
  • 4
  • 24
  • 42
Ilya Chernov
  • 411
  • 7
  • 18

1 Answers1

13

try filtering out the empty rows...

=QUERY('OtherListName'!A1:C; "Select A, B, C where A <>'' order by A, B";1)

Or, if the data in A is numeric

=QUERY('OtherListName'!A1:C; "Select A, B, C where A is not null order by A, B";1)
JPV
  • 26,499
  • 4
  • 33
  • 48
  • At first I thought it was strange that this made a difference... and then I realized the reason why it does: the empty rows are sorting to the top, so it looks like you have no data, but in fact, if one scrolls down, there's sorted data below the empty rows. – lindes Aug 11 '23 at 14:52