At what point gets a custom retrieve of a certain amount of data sorted?

0
In a retrieve action I'm using the custom option to only get a certain amount of sorted data. Now I'm wondering at what stage the sorting is done. Is this done on DB level after which the specified amount of that sorted data is retrieved, or does is first retrieve the specified amount, which it then sorts. I was looking over the documentation, but I can't find a definitive answer to this question. Thank you in advanced.
asked
4 answers
2

Data is sorted in the database before return data is selected. Thus the list which will be returned is 1 2 4 6

answered
1

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.

answered
1

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

answered
0

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.

answered