I know how to use the GROUP BY clause in the QUERY function with either a single or multiple fields. This can return the single row per grouping with the maximum value for one of the fields.
This page explains it nicely using these queries and image:
=query({A2:B10},"Select Col1,min(Col2) group by Col1",1)
=query({A14:C22},"Select Col1,Col2,min(Col3) group by Col1,Col2",1)
However, what if I only want a query that returns the corresponding values for the most recent row, grouped by multiple fields? Is there a query that can do this?
Example
Source Table
| created_at | first_name | last_name | address | city | st | zip | amount | |
|---|---|---|---|---|---|---|---|---|
| 4/12/2022 19:15:00 | Ava | Anderson | ava@domain.com | 123 Main St | Anytown | IL | 12345 | 1.00 |
| 8/30/2022 21:38:00 | Brooklyn | Brown | bb@domain.com | 234 Lake Rd | Baytown | CA | 54321 | 2.00 |
| 2/12/2022 16:58:00 | Ava | Anderson | ava@new.com | 123 Main St | Anytown | IL | 12345 | 3.00 |
| 4/28/2022 01:41:00 | Brooklyn | Brown | brook@acme.com | 456 Ace Ave | Bigtown | NY | 23456 | 4.00 |
| 5/03/2022 17:10:00 | Brooklyn | Brown | bb@domain.com | 234 Lake Rd | Baytown | CA | 54321 | 5.00 |
Desired Query Result
Group by first_name, last_name, address, city, st, and zip, but return the created_at, email, and amount for the maximum (most recent) value of created_at:
| created_at | first_name | last_name | address | city | st | zip | amount | |
|---|---|---|---|---|---|---|---|---|
| 4/12/2022 19:15:00 | Ava | Anderson | ava@domain.com | 123 Main St | Anytown | IL | 12345 | 1.00 |
| 8/30/2022 21:38:00 | Brooklyn | Brown | bb@domain.com | 234 Lake Rd | Baytown | CA | 54321 | 2.00 |
| 4/28/2022 01:41:00 | Brooklyn | Brown | brook@acme.com | 456 Ace Ave | Bigtown | NY | 23456 | 4.00 |
Is such a query possible in Google Sheets?
