-1

I have got a Google sheets table called Purchases with various columns for different items to purchase. The first column is called Status, and it includes values like: 

Research, Approval, Purchasing, Shipping, Invoicing, Complete.

I want to write an SQL statement to retrieve all orders that are not Complete, but I would like to ordered them so that all the Research ones go first, Approvals second, Purchasing third and so on.

My current formula is:

=QUERY(Purchases;SELECT * WHERE A <> 'Completo';1)

This retrieves the correct items but they are mixed and not in the order that I want. Can someone help me to do better than this? Thanks a lot!

TheMaster
  • 45,448
  • 6
  • 62
  • 85
Simone Romeo
  • 59
  • 3
  • 11

1 Answers1

1

You need to make a custom sorting order by values from a list.

Assuming, your data is in range A:B:

=QUERY({arrayformula( VLOOKUP(A:A, {{"Research";"Approval";"Purchasing";"Shipping";"Invoicing"},{1;2;3;4;5}},2,)), A:B}, "SELECT Col2, Col3 WHERE Col2 <> 'Complete' and Col2 <> '' order by Col1",1)

Notes:

  • {{"Research";"Approval";"Purchasing";"Shipping";"Invoicing"},{1;2;3;4;5}} is a list of values for sorting, you may use a range instead.
  • Change A:B to your Purchases range
  • Need to use Col1 notation in a query, because source data will be an array, not range.
  • we sort by Col1. This column contains numbers, so the sort is legal.
  • You may use select *, but it will return the column with numbers:
Max Makhrov
  • 17,309
  • 5
  • 55
  • 81