How to select more than 2 columns in =QUERY()

Question

I was trying to copy data from Sheet1 to Sheet2 using query. But I want to select MAX(A) and all the other columns where data exists (B,C,D...etc.) and group by B.

То clarify, in Sheet1 I have some similar rows which differ in col.A, which contains Timestamp, and a few other columns. I want to fetch only the latest record from each set of similar records.

So my Query is

=QUERY('Sheet1'!A3:S; "select MAX(A),B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S group by B")

It gives me error #Value! Unable to parse query string to FUNCTION query parameter 2 ADD_COL_TO_GROUP_BY_OR_AGG:C,...

Answer 1

I used this function =QUERY('Sheet1'!$A$1:S;"select MAX(A) group by B label MAX(A) '' ";2) to populate the first column in Sheet2 and then for each column this function =ArrayFormula(LOOKUP($A$3:$A;'Sheet1'!$A$3:$A;'Имот'!B3:B)).

Basically the second function searches for match from the cell in column A in the same table with a cell in column A in the first table, gives the result of the current column and all this copies itslef in the whole column.

After that I copied the formula manually for each column. And my problem is already solved.

Answer 2

The other columns need to be in the 'group by' to. See if this works

=QUERY('Sheet1'!A2:S, "select MAX(A),B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S group by B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S")