in search window how to restrict retrieve(retrieve only one page and wait for selecting other page)

[powerbuilde]

I search in windows (that contain dw retrieve its data from sqlserver database)either by using argument or filter

if there are 5000 rows(for example) that satisfy search criteria

i don't want to retrieve all rows that satisfy search criteria , i want to retrieve only first 50 rows and display it in window - (as database is on web) and displaying (in bottom of window ) numbers to indicate there are other output pages for search (i can not use retrieve as neede as i had sor and search may lock table in multi user whene retrieve rows as needed

when user click (for example no 3 dw begins to retrieve the third page according to search criteria and if user click no 9 dw begins to retrieve the 9th page according to search criteria)

how can i do this (with out using rows as needed as i had sort and in multi user it may lock table)

as for example any page on web that you search for specific product in amazon as shown below

as seen in the last line of amazon enter image description here

Previous Page 1 2 3 ... 154 Next Page

 

[powerbuilde]

Hi



Tóth Péte replied "we use informix: select skip 10 first 10 * from table order by …" i found similar in sql server 2012 that is "

OFFSET N ROWS FETCH NEXT X ROWS ONLY

In SQL server 2008 i can use

ROW_NUMBER() OVER( as replied from Gökalp AKYÜZ) but offset and fetch is more easier

as Roland Smith suggested to alter sql synatx in sqlpreview event of DW i wrote thes code in the sqlpreview event

ls_sql = dw_1.GetSQLSelect()

SqlSyntax = ls_sql + ' ORDER BY import_letter.im_letter_serial DESC, import_letter.im_letter_no DESC OFFSET ' + string(ii_offset) + ' ROWS FETCH NEXT 10 ROWS ONLY'

setsqlpreview(SqlSyntax)

where ii_offset is instance var -its initial val = 0 and when i use next page ii_offset = ii_offset + 10

it works fine but also there are noticeable delay time (may be as i try on server with 8 GB ram only)

Best Regrds