Using Sort() Function

AppSheet AppSheet Q&A
1

Hi,

I’m using Top(Sort(Select())) in the expression below and expecting to retrieve the [Group Level] for last row it finds but it is giving me the first row. What am I missing?

TOP(SORT(select(Event Rider Details[Group Level], [_THISROW].[TransponderID]=[Transponder ID]),FALSE),1)

Thanks.

2

Please try

TOP(SORT(select(Event Rider Details[Group Level], [_THISROW].[TransponderID]=[Transponder ID]), TRUE),1)

3

Tried it. Same result.

Thanks,

4

What’s your definition of “last row?”

5

The last row in the table for that condition on [TransponderID] (row highlighted in the screenshot). In the table there’s are two entries for TransponderID “00001”. I want to to result in the bottom entry. So, I want the field to result in [Group Level] “3”, but it keeps resulting in “2”.

But ultimately, once I get this figured out, I will want it to be the row with the newest [Timestamp].

AdemarN_0-1693154094236.png
AdemarN_0-1693154094236.png1340×243 21.4 KB

Thanks.

6

I think you will need to create a VC to first orderby the column TimeStamp to make sure that the list of values is orderer as you want and then filter this list again and take the value you need.

Also, remember that TOP() returns a list, not a value.

You will find many posts about this in the community under “Select and Orderby”

7

Not sure I understand how to use a Virtual Column to sort. Can you elaborate? Also, “List” rather than “Value”. Can you suggest a different approach. I just want to return one text value, the one having the latest Timestamp, from a list of many rows…

8

Ok I think I got it. @SkrOYC let me on to something so I did this…

I created a VC on the [Event Rider Details] table that returns a yes/no if the reocord is the Latest Record:

[vcLatest Record] = IF(MAXROW(“Event Rider Details”,“Timestamp”,([TransponderID]=[_THISROW].[TransponderID]))=[Row ID],“YES”,“NO”)

Then on the other table I filtered on [vcLatest Record] = “YES”:

top(select(Event Rider Details[Group Level],AND([_THISROW].[TransponderID]=[TransponderID],[vcLatest Record]=“YES”)),1)

We’re good to go.

Thanks All.

9

I’m happy you found a solution, but sadly it’s not the most efficient.

Please check this post:

Solved: Re: SELECT, AND then ORDERBY (date) - Google Cloud Community

2 Likes