Skip to Content
0

How to SELECT TOP 100 and then (if i choose) the following 100 until end of rows?sql server

Nov 20, 2016 at 09:17 PM

254

avatar image

Hi

in powerbuilder app in open event of window that contains dw_1 script containe dw_1.retrieve()
as i have thousands of rows then in dw_1 in sql syntax i added top 100 after select select top 100 col_name from tab_name
thus i retrieve only 100 rows from table
now i want to put buttons on the window (next-prev) when user clicked it he retrieve next/prev 100 rows (according to rows already retrieved) and when it is last/first row next/prev is disabled
also when i try to search and pass arg to dw (dw.retrieve(arg)) as pb search in 100 row that already displayed i want when to begin search pb search on all rows (ie clear top 100 in the select stat,) and return only 10 rows that satisfy what i search displaying button (next/prev) to show following rows

Best Regards

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

5 Answers

Eduardo G. Nov 22, 2016 at 07:13 AM
0
Share
10 |10000 characters needed characters left characters exceeded
ahmed tawfik Nov 22, 2016 at 08:24 AM
0

Retrieving rows as needed may had following problems

In a multiuser situation, Retrieve Rows As Needed might lock other people out of the tables.

and

Retrieve Rows As Needed is overridden if you have specified sorting

Share
10 |10000 characters needed characters left characters exceeded
A V Nov 23, 2016 at 09:08 PM
0

You should try it in a multi user setup and see. If your datawindow is readonly (no updates) then you should be ok and instead of specifying a sort expression, user ORDER BY in your query.

You can also create views on the database to prevent locking but that will be more involved.

Share
10 |10000 characters needed characters left characters exceeded
ahmed tawfik Nov 25, 2016 at 01:57 PM
0

Hi

thank for every one replied to me

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

Share
10 |10000 characters needed characters left characters exceeded
m s Nov 25, 2016 at 04:23 PM
0

use of 'next X' gets slower over large number of records since it has to count them.

use value based limits instead of row limits to make it fast. something like:

select top 100 mycolumn
from mytable
WHERE mycolumn > :ll_lastretrievedid
order by mycolumn
Share
10 |10000 characters needed characters left characters exceeded