[SOLVED] Is there any query string that I can use with the QUERY function to get a group-wise maximum?

Issue

This Content is from Stack Overflow. Question asked by dhalsim2

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)

Google Sheets using GROUP BY

However, what if I only want a query that returns the corresponding values for the most recent row, grouped by a multiple fields? Is there a query that can do this?

Example

Source Table

created_atfirst_namelast_nameemailaddresscitystzipamount
4/12/2022 19:15:00AvaAndersonava@domain.com123 Main StAnytownIL123451.00
8/30/2022 21:38:00BrooklynBrownbb@domain.com234 Lake RdBaytownCA543212.00
2/12/2022 16:58:00AvaAndersonava@new.com123 Main StAnytownIL123453.00
4/28/2022 01:41:00BrooklynBrownbrook@acme.com456 Ace AveBigtownNY234564.00
5/03/2022 17:10:00BrooklynBrownbb@domain.com234 Lake RdBaytownCA543215.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_atfirst_namelast_nameemailaddresscitystzipamount
4/12/2022 19:15:00AvaAndersonava@domain.com123 Main StAnytownIL123451.00
8/30/2022 21:38:00BrooklynBrownbb@domain.com234 Lake RdBaytownCA543212.00
4/28/2022 01:41:00BrooklynBrownbrook@acme.com456 Ace AveBigtownNY234564.00

Is such a query possible in Google Sheets?



Solution

Use this formula

=QUERY({QUERY(A1:I, " Select max(A),min(B),min(C),min(D),min(E),min(F),min(G),min(H),min(I) Group by B,C,E,F,G,H ", 1)},
                    " Select *  Where Col1 is Not null ")

enter image description here


This Question was asked in StackOverflow by dhalsim2 and Answered by Osm It is licensed under the terms of CC BY-SA 2.5. - CC BY-SA 3.0. - CC BY-SA 4.0.

people found this article helpful. What about you?