Data is sorted in the database before return data is selected. Thus the list which will be returned is 1 2 4 6
The sorting happens at database level.
If this wasn’t so, no single Mendix application could handle a scheduled event on a full table with for example 1 milion records.
Practical: Create a Integer variable ($Offset) then do the custom retrieve with Offset: $Offset, Amount: (e.g. 100), Do the logic you need to do in a loop, then after the loop do a count on the retrieved list to see if the amount is the amount you have set to retrieve (e.g. 100), then a descision to see if you got 100 records (if you got less then you know that there will be no more records to handle left) if you got 100 records you can expect that there are more to go so change the variable $Offset to $Offset + (e.g.100) and merge back in right before your retrieve, and the next records will be loaded and handled.
Thank you for your answer.
I'm afraid my question was not precise enough, since I'm still don't know for certain which part is executed first. The sorting of the list or determining the $Amount of data first which is then sorted.
What I wonder is if I have the following list: 8 2 9 1 6 4 7 and I want to retrieve an ascending amount of 4 numbers, does it give me back 1 2 8 9 or 1 2 4 6.
The database query will be something like this:
SELECT number FROM table ORDER BY number LIMIT 4;
FIrst the ordering takes place and then the select with the limit.
For reference see: https://madusudanan.com/blog/all-you-need-to-know-about-sorting-in-postgres/
And I expect other databases to do the same, although not looked at