SQL OrderBy help

Questions
1

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:

|8/1/2019|100|1|
|8/1/2019|110|2|
|8/1/2019|115|3|
|8/1/2020|102|1|
|8/1/2020|112|2|
|8/1/2020|117|3|

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 )

2

Specify the sort direction for each field. Default is ASC.

In your case, I think you would have better luck with

ORDER BY StartDate DESC, Grade DESC

See the following reference.

Hope this helps.

5 Likes
3

I wonder if the date format could be tricky. SQL dates are YYYY/MM/DD if I am right. Here it seems date format is either DD/MM/YYYY or MM/DD/YYYY - it's always hard to figure out which of the first two numbers is the day.

1 Like
4

Yes, the fact that FileMaker can't seem to get into the whole ISO 8601 date standard after all these years is ridiculous. This product goof is most apparent with SQL I've found.

5

Nothing technically tricky here. @JasonMark mentions that dates order DESC when DESC is specified and ASC otherwise.

We see the author's representation of dates in the original post. It may or may not be what is displayed in FileMaker. Furthermore, we see formatted dates in FileMaker. FileMaker's internal representation of a date is a number.

1 Like
6

You're a lifesaver. I tried putting commas in all sorts of inappropriate places.

1 Like