I have a bunch of students who took standardized tests over the past 3 years, and I have a lookup table which needs to look up what grade level they tested into based on which year and trimester they took the test in. The lookup table looks like this:
So a student who gets 111 on their test on 9/1/2019 would show up as grade 2, and a student who gets a 111 on their test on 8/2/2020 would still be in first grade.
I wrote a sql call which finds all lookups but I can't get the one I want to show up at the top!
When I say
ORDER BY StartDate DESC
it puts sorts by date correctly, but the grade levels are in ascending order.
When I say
ORDER BY Grade DESC
it puts sorts by grade level correctly, but the start dates are in ascending order.
When I try
ORDER BY StartDate,Grade DESC
it sorts by StartDate Ascending and Grade Level descending.
How do I get it to sort BOTH start date and grade descending?
Complete Code here:
ExecuteSQL ( "SELECT GradeLevel,StartDate FROM NWEA_GradeLevel_Lookup WHERE Score < ? AND StartDate < ? ORDER BY StartDate,Score DESC FETCH FIRST 1 ROWS ONLY "; "" ; "" ; $TestScore ; $TestDate )