Google Sheets Return Any(All) Row Value(s) For MAX-GROUP Query-CodePudding

I am looking to return non-grouped row values from a query of a table sorted by the MAX value of a column, within a group.

DATA TABLE | NAME | ASSET | ACTION | DATE | |--|--|--|--| | JOE | CAR | BOUGHT | 1/1/2020 | | JANE | HORSE | BOUGHT | 1/1/2021 | | JOE | HORSE | BOUGHT | 2/1/2021 | | JANE | HORSE | SOLD | 3/1/2021 | | JOE | CAR | SOLD | 1/1/2022 | | JOE | CAR | BOUGHT | 2/1/2022 |

For the table above, I presented the following code. =QUERY(A1:D5,"SELECT A,B,C,D, MAX(D) GROUP BY A,B",TRUE)

The following TARGET TABLE is output I'm looking for: | NAME | ASSET | ACTION | DATE | |--|--|--|--| | JANE | HORSE | SOLD | 3/1/2021 | | JOE | HORSE | BOUGHT | 2/1/2021 | | JOE | CAR | BOUGHT | 2/1/2022 |

However, because 'C' is not included in the GROUP, the formula returns an error. "Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGG: C"

If I were to omit COL C & D, "ACTION" & "DATE" from the SELECT: =QUERY(A1:D5,"SELECT A,B, MAX(D) GROUP BY A,B",TRUE) , I have the correct record rows, but am missing the STATUS.

MAX-DATE TABLE | NAME | ASSET | max DATE | |--|--|--| | JANE | HORSE | 3/1/2021 | | JOE | HORSE | 2/1/2021 | | JOE | CAR | 2/1/2022 |

OR, when I add COL C as a "PIVIOT": =QUERY(A1:D5,"SELECT A,B, MAX(D) GROUP BY A,B PIVOT C",TRUE)I have the correct record rows, but do not have the 'current' STATUS within the record row.

PIVOT ACTION TABLE | NAME | ASSET | BOUGHT | SOLD | |--|--|--|--| | JANE | HORSE | 1/1/2021 | 3/1/2021 | | JOE | HORSE | 2/1/2021 | | | JOE | CAR | 2/1/2022 | 1/1/2022 |

Still I have not found a method to create my TARGET TABLE. Am I overlooking a method to include a non-grouped field into a query using MAX()? Or is it impossible within Google Sheets Query without JOIN functions?

(I hope it is obvious that I desire to apply this to a large and dynamic dataset.)

Thank you for your insight. Cheers!

CodePudding user response：

It's not that flexible to work with QUERYs with its aggregation requisites and so on.

You can create a filter, by comparing column D with a "fictional" column created with BYROW: = BYROW(A2:A,LAMBDA(each,MAXIFS($D$2:$D,$A$2:$A,each,$B$2:$B,OFFSET(each,,1))))

That would look like this (I highlighted the matches and added extra rows for reference):

Then, you can set this filter (don't create this column, it's just a visualization of what I did):

=FILTER(A2:D,D2:D = BYROW(A2:A,LAMBDA(each,MAXIFS($D$2:$D,$A$2:$A,each,$B$2:$B,OFFSET(each,,1)))))

This way, you're comparing the dates with the maximum for each category