How to use QUERY in Sheets to find most recent grade for each student?

0
votes

I have a spreadsheet containing a list of assessments for students. It is comprised of four columns:

   A           B         C         D
Student     Standard    Date     Score

On another sheet I have a table containing a list of students in column A and a list of standards in Row 1.

I want to query the spreadsheet of assessments to return the score of the most recent assessment for the current student on the current standard. Currently I have this formula in cell K3:

=Query(Assessments!A:D,"select D where(A="&$A3&" and B="&K$1&") order by max(C) limit 1")

but it gives me this error:

Unable to parse query string for Function QUERY parameter 2: AGG_IN_ORDER_NOT_IN_SELECTMAX(C)

Edit: I've gotten some great answers, but I guess I asked poorly. What I really need is the score from the most recent assessment. The results from JPV and pnuts have both given the date of the most recent assessment. Here is a stripped down version of the actual file: spreadsheet.

In the Students sheet I'm needing a formula in the Green cells that results in a number from 0 to 4 based on the data in the Assessments sheet.

This is why in the query I was trying to select D, but order by max(C).

I tweaked one of these formulas and got: 

=iferror(query(A3:D20, "Select D where A = '"&G20&"' and B = '"&I20&"' order by C desc limit 1",0), "no data found")
2
google-sheets
You would have to use max (which is an AGGREGATE functione ) in the select clause. If you need help, please consider some example data. – JPV

2 Answers

2
votes

I addition to the fine solution provided by pnuts (+1 for the sample data), here are some posibilities using query (check the green cells in this spreadsheet.

=query(A2:D20, "select A, MAX(C) GROUP BY A  pivot B",1)

or

=query(A2:D20, "select A, B, MAX(C) GROUP BY A, B",1)

should create a table with the latest date per student, per standard.

In case you want to use cell references (where a cell holds a student name and another cell holds the standard), try:

=iferror(query(A3:D20, "Select A, B, C where A = '"&G20&"' and B = '"&I20&"' order by C desc limit 1",0), "no data found")

where G20 is student name and I20 is standard (change range to suit).

1
votes

Does not use the query function but seems to "query the spreadsheet of assessments to return the most recent assessment for the current student on the current standard": 

=iferror(max(filter(Assessments!$A$1:$D$99,Assessments!$A$1:$A$99=$A3,Assessments!$B$1:$B$99=K$1)),"")

Constructed data as example: 

Student Standard    Date    Score
Bod5        C   2/2/2015    9
Bod6        B   1/1/2015    8
Bod7        C   7/7/2015    7
Bod8        A   9/9/2015    6
Bod1        B   3/3/2015    5
Bod2        C   4/4/2015    43
Bod3        B   6/6/2015    2
Bod4        C   1/1/2015    1
Bod1        A   1/1/2016    8
Bod1        A   2/2/2017    7
Bod1        A   1/1/2013    6
Bod1        A   1/1/2011    5
Bod9        A   9/9/2009    9
Bod9        B   1/1/2011    3
Bod9        C   3/3/2013    2
Bod9        A   10/10/2010  4
Bod9        B   11/1/2001   2
Bod9        C   4/4/2014    1

Output:

SO33313591 example