2

I am trying to use the QUERY function on a list of Google Form responses to only display the most recent of unique entries(based on the key Student ID).

A = Timestamp Column 
C = Student ID Column

Using:

select max(A)
group by C

I was able to get the right entries, but only one column for each(I want all the data, the whole row, from the right entries)

So I tried using:

select max(A),B,C,D,E,F,G,H 
group by C

But this gives an error

Unable to parse query string for Function QUERY parameter 2: ADD_COL_TO_GROUP_BY_OR_AGGB

I am new to databases, first time using QUERY. Sorry if this is confusing, but maybe the sheet makes more sense: spreadsheet

Rubén
  • 34,714
  • 9
  • 70
  • 166
Halfdane
  • 100
  • 1
  • 7

2 Answers2

2

Does this formula work as you want:

={Raw!A1:J1;FILTER(Raw!A2:J,MMULT((Raw!C2:C=TRANSPOSE(Raw!C2:C))*(Raw!A2:A<TRANSPOSE(Raw!A2:A)),SIGN(ROW(Raw!A2:A)))=0)}

Chris Hick
  • 3,004
  • 1
  • 13
  • 15
  • Thank you! Your formula gives me the output I was looking for. It seems reasonable to the QUERY function should be able to do the same thing but there must be something I am missing. Maybe Google's QUERY is just not as functional as other Query languages. – Halfdane Jul 26 '15 at 20:39
2

In addition to Chris' contribution and using QUERY():

=ArrayFormula(vlookup(query({row(Raw!A:A),sort(Raw!A:J)},
"select max(Col1) where Col4 is not null group by Col4 label max(Col1)''",0),
{row(Raw!A:A),sort(Raw!A:J)},{2,3,4,5,6,7,8,9,10,11},1))
Rubén
  • 34,714
  • 9
  • 70
  • 166
JPV
  • 26,499
  • 4
  • 33
  • 48
  • Thank you JPV! This looks like the best we can do with the QUERY function. I was hoping that I missed something obvious and that the solution would be elegant, but oh well. I looked into using a natural join to solve this with a 2 part solution, but it looks like Google sheets can't do that either! – Halfdane Jul 27 '15 at 09:46