Search code examples
arraysgoogle-sheetsgoogle-sheets-formulagoogle-sheets-querygoogle-query-language

Is it possible to implement multi-stage SORT using QUERY?


https://docs.google.com/spreadsheets/d/1XoxAY8MEgrGrpG1yJ2VthHDOGr6u2pBZC1YotpDfslE/edit?usp=sharing

Tab Deck Trends cell A3 contains the QUERY command where you can see the current sorting order.

What I am wanting to achieve is the following:

Any row where Col E equals 4, I would like the secondary sorting to be based on Col F

All other rows I would like the secondary sorting to be based on Col D (as it currently is set).

Is it even possible to have multiple sorting rules in one QUERY?


Solution

  • try:

    =QUERY({QUERY({
     'Combined Opp Meta'!A2:G11; 'Combined Opp Meta'!A14:G23; 'Combined Opp Meta'!A26:G35},
     "where Col5 = 4
      order by Col5 desc,Col6 desc", 0);
     QUERY({
     'Combined Opp Meta'!A2:G11; 'Combined Opp Meta'!A14:G23; 'Combined Opp Meta'!A26:G35},
     "where Col5 <> 4
      order by Col5 desc,Col4 desc", 0)},
     "limit 12", 0)
    

    array literal fix:

    =QUERY({IFERROR(QUERY({
     'Combined Opp Meta'!A2:G11; 'Combined Opp Meta'!A14:G23; 'Combined Opp Meta'!A26:G35},
     "where Col5 = 4
      order by Col5 desc,Col6 desc", 0), {"","","","","","",""});
     IFERROR(QUERY({
     'Combined Opp Meta'!A2:G11; 'Combined Opp Meta'!A14:G23; 'Combined Opp Meta'!A26:G35},
     "where Col5 <> 4
      order by Col5 desc,Col4 desc", 0), {"","","","","","",""})},
     "where Col1 is not null limit 12", 0)