LIMIT & OFFSET

  

Hi,

Can you tell me, how to use LIMIT & OFFSET in SQL in OutSystems.

Thanks in Advance.

Hi,


If you're talking about SQL advanced: https://www.techonthenet.com/sql/select_limit.php

If you're talking about aggregates, you have, in properties area, one field where you configure the MAX records retrieved by the query:

 

You can define here the Maximum records retrieved.


Hope this can help. ;)


Best regards,

Ricardo

Hi,

There is any reason for you to want to create your own pagination.

Outsystems pagination isn't enough for you? https://www.outsystems.com/forums/discussion/37002/how-the-pagination-navigation-widget-works/

Regards,

Marcelo

Hi Ricardo,

Thanks for the responds.

I need SQL query and i saw your link. Then i tried that syntax but i got error.

Below i attached the simple query and its error also.

Can you give the correct syntax please.


Thanigai Arasu wrote:

Hi Ricardo,

Thanks for the responds.

I need SQL query and i saw your link. Then i tried that syntax but i got error.

Below i attached the simple query and its error also.


Microsoft SQL Server don't use LIMIT, use TOP. Did you try it? I don't know if this is the case. Can you share with us your SQL statement?


Marcelo Ferreira wrote:

Hi,

There is any reason for you to want to create your own pagination.

Outsystems pagination isn't enough for you? https://www.outsystems.com/forums/discussion/37002/how-the-pagination-navigation-widget-works/

Regards,

Marcelo


Thanks for the responds Marcelo Ferreira

Pagination flow really useful to me, but i need SQL Syntax.


Ricardo Pereira wrote:

Thanigai Arasu wrote:

Hi Ricardo,

Thanks for the responds.

I need SQL query and i saw your link. Then i tried that syntax but i got error.

Below i attached the simple query and its error also.


Microsoft SQL Server don't use LIMIT, use TOP. Did you try it? I don't know if this is the case. Can you share with us your SQL statement?



Yes Ricardo, I used that but that only fetch rows from the top only, but i need to fetch rows from mid, So i need to give offset(started row number) and limit. 


Solution

Hi,

Here is my needs,

I have a table(Student) which contain 100 rows, Now i need to fetch 51-60 records only, Can you tell me the query.

In MySQL "Select * from Student limit 10 offset 51;" will work but in outsystems, how can i achieve this.

Hi Alberto Ferreira,

Thanks for responds,

I tried that but still i getting error, i've attached that error below. Can you correct that please.

Alberto Ferreira wrote:

Hello Thanigai


Check 


https://www.outsystems.com/forums/discussion/42476/need-to-understand-list-navigationpaging-with-aggregates/

it can help


Regards


Thank you so much Alberto, Its working but if only with order by fetch and offset will work otherwise its not working, anyway thank you its useful.


Something like this:

BETWEEN @offset+1 AND @offset+@count;


This can help you?

Thanigai Arasu wrote:

Hi Alberto Ferreira,

Thanks for responds,

I tried that but still i getting error, i've attached that error below. Can you correct that please.


Hello Thanigai


You must us it in ORDER BY expression


Regards

Alberto Ferreira wrote:

Thanigai Arasu wrote:

Hi Alberto Ferreira,

Thanks for responds,

I tried that but still i getting error, i've attached that error below. Can you correct that please.


Hello Thanigai


You must us it in ORDER BY expression


Regards


Its working with order by Alberto, Thank you so much.

Ricardo Pereira wrote:

Something like this:

BETWEEN @offset+1 AND @offset+@count;


This can help you?

I can't understand this Ricardo.

Can you give briefly.


Thanigai Arasu wrote:

Ricardo Pereira wrote:

Something like this:

BETWEEN @offset+1 AND @offset+@count;


This can help you?

I can't understand this Ricardo.

Can you give briefly.



One example:

SELECT * 
FROM Sponsors 
WHERE RowNumber BETWEEN 10 AND 20;


In this link you have more detail: https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server


Ricardo Pereira wrote:

Thanigai Arasu wrote:

Ricardo Pereira wrote:

Something like this:

BETWEEN @offset+1 AND @offset+@count;


This can help you?

I can't understand this Ricardo.

Can you give briefly.



One example:

SELECT * 
FROM Sponsors 
WHERE RowNumber BETWEEN 10 AND 20;


In this link you have more detail: https://stackoverflow.com/questions/603724/how-to-implement-limit-with-microsoft-sql-server


Thank you so much Ricardo. I got clear mind about it because of this.