Search code examples
sqlgoogle-sheetssubquerygoogle-query-language

Possible to do subselect in Google Sheets

I have the following data in the movies data range:

enter image description here

I would like to do a subselect to get the highest-grossing movie for that director. I can do it by adding a new column like this:

enter image description here

Note that I've used the hacky 'nested-query' notation to remove the header row and just return a single scalar value:

=QUERY(QUERY(movies, "SELECT MAX(C) WHERE A='"&A2&"' GROUP BY A", 0), "SELECT * OFFSET 1", 0)

However, I was wondering if I could just do a single query on the director|movie|boxoffice columns with a subselect within the query statement, I suppose it would come out to something like:

=QUERY(movies, "SELECT A, B, C, (SELECT MAX(C) WHERE A='"&A2&"' GROUP BY A)", 0)

I believe the answer to this is a straight 'no', but I was curious if there's any sort of sub-query composability within the google sheets query language, or if I just need to sort of figure out workarounds here?

https://developers.google.com/chart/interactive/docs/querylanguage

Solution

  • try:

    =INDEX(IFNA(VLOOKUP(A2:A, SORT(A2:C, 3, ), 3, )))

    enter image description here

    or whole:

    =INDEX({A1:C, {"highestgrossing"; IFNA(VLOOKUP(A2:A, SORT(A2:C, 3, ), 3, ))}})

    enter image description here