Hi @Tjors willsmit 

Add this to your query:

ORDER BY @TableSort
OFFSET @StartIndex ROWS
FETCH NEXT @MaxRecords ROWS ONLY; 

You will need to get the count to feed the pagination widget. 

For this, you will have to replicate the same query and simple get the count

I did it in your .oml, kindly check if this solves your problem

Hi,

Or you can do it all in one query by adding an extra output structure of type integer. Then use the Count(*) OVER() function in your select. This will get a total count over all the records that meet your filter criteria.

Together with OFFSET @StartIndex ROWS FETCH NEXT @MaxRecords  ROWS ONLY  you can only return the number of rows you want per page for your pagination.

So in your query this will be:

It is also explained in more detail here: https://johnalvinsalamat.medium.com/optimizing-paged-sql-queries-in-outsystems-22a13af083ba

Hi @Tjors willsmit ,

Please add below line in select statement, it will return all count and it can be used for pagination. I have tested and it is working perfectly and in this way there is no need to execute another sql query.

count(1) Over() cnt

Regards

Shashi Kant Shukla