Sheets Query No Sort

0
votes

Is there a way for a query to output unsorted? By default, it sorts the data.

I tried adding a helper column that adds the row number to the query input, but then it is not part of "select" like this:

=QUERY({Data!A1:C24,ArrayFormula(if(Data!A1:A24<>"",row(Data!A1:A24),""))},"Select Col1, Sum(Col2) group by Col1 pivot Col3 order by Col4")

Here is a sample sheet: https://docs.google.com/spreadsheets/d/1bMdCQKeWKqXI2Mmwpu__lHsGO2OttbJArWusBS8BMyQ/edit?usp=sharing

The desired output is that the query should show the points sum first for Apple, Orange, Banana etc. Screenshot of sample sheet

2
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygoogle-query-language

2 Answers

2
votes

try:

=ARRAYFORMULA(IFNA(VLOOKUP(UNIQUE(Data!A1:A), 
 TRANSPOSE(QUERY(TRANSPOSE(QUERY({Data!A1:C},
 "select Col1,sum(Col2)
  group by Col1
  pivot Col3")), 
 "order by Col1 desc", 1)), 
 {TRANSPOSE(ROW(INDIRECT("A1:A"&COUNTA(UNIQUE(Data!C2:C))+1)))}, 0)))

0

1
votes

Another way:

=sort(QUERY(Data!A1:C24,"Select A, Sum(B) group by A pivot C ",1),
match(query(A1:A24,"select min(A) group by A label min(A) 'Name'",1),A1:A24,0),1)

enter image description here

Or a bit shorter:

=sort(QUERY(Data!A1:C24,"Select A, Sum(B) group by A pivot C ",1),
match({A1;sortn(A2:A24,999,2)},A1:A24,0),1)