Discussion:
Paging results
(too old to reply)
Jorge Reyes
2010-02-18 18:02:58 UTC
Permalink
Hi everyone, first im sorry because i dont know how to explain my
issue so i have to put a fragment of my code which is written in php
and mysql, my goal is transfer this querys to sybase, hope im not
disturbing and somebody can help me:

$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize = 10;

$sql = "select count(*) as cnt from orders";
$handle = mysql_query($sql);
$row = mysql_fetch_object($handle);
$totalRec = $row->cnt;

//make sure pageNo is inbound
if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
$pageNo = 1;
}


//page index starts with 1 instead of 0
$sql = "select * from orders limit " . ($pageNo - 1)*$pageSize . ",
" . $pageSize;

This last query is the problem, as you can see this allows me to
paging the results increasing the $pageNo so i can ask for page 1 with
10 results, then page 2 with the next 10 and so on. How can i make
this with T-SQL?

Thanks in advanced.
Bret_Halford
2010-02-18 18:39:01 UTC
Permalink
Post by Jorge Reyes
Hi everyone, first im sorry because i dont know how to explain my
issue so i have to put a fragment of my code which is written in php
and mysql, my goal is transfer this querys to sybase, hope im not
$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize = 10;
$sql = "select count(*) as cnt from orders";
$handle = mysql_query($sql);
$row = mysql_fetch_object($handle);
$totalRec = $row->cnt;
//make sure pageNo is inbound
if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
  $pageNo = 1;
}
//page index starts with 1 instead of 0
$sql = "select * from orders limit " . ($pageNo - 1)*$pageSize . ",
" . $pageSize;
This last query is the problem, as you can see this allows me to
paging the results increasing the $pageNo so i can ask for page 1 with
10 results, then page 2 with the next 10 and so on. How can i make
this with T-SQL?
Thanks in advanced.
Unfortunately, you aren't clear about *which* Sybase database product
you
are going to be using, and it does make a difference. There are
four: Adaptive
Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase IQ
(IQ), and
Advantage Database Server.

However, in general (and perhaps assuming ASE, which is what I'm most
familiar with)

One method is to select the results into a temp table adding a
rownumber field,
you can then

"select ... from temptable where rownum between ((pageno -1) * 10) +
1 and (pageno * 10) "

More details at http://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12

If using ASE 15.x, you might also consider using a scrollable cursor
to accommodate paging
backwards and forwards through the result set.

http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sqlug/html/sqlug/sqlug788.htm
Jorge Reyes
2010-02-19 18:13:37 UTC
Permalink
Post by Bret_Halford
Post by Jorge Reyes
Hi everyone, first im sorry because i dont know how to explain my
issue so i have to put a fragment of my code which is written in php
and mysql, my goal is transfer this querys to sybase, hope im not
$pageNo = $json->{'pageInfo'}->{'pageNum'};
$pageSize = 10;
$sql = "select count(*) as cnt from orders";
$handle = mysql_query($sql);
$row = mysql_fetch_object($handle);
$totalRec = $row->cnt;
//make sure pageNo is inbound
if($pageNo<1||$pageNo>ceil(($totalRec/$pageSize))){
  $pageNo = 1;
}
//page index starts with 1 instead of 0
$sql = "select * from orders limit " . ($pageNo - 1)*$pageSize . ",
" . $pageSize;
This last query is the problem, as you can see this allows me to
paging the results increasing the $pageNo so i can ask for page 1 with
10 results, then page 2 with the next 10 and so on. How can i make
this with T-SQL?
Thanks in advanced.
Unfortunately, you aren't clear about *which* Sybase database product
you
are going to be using, and it does make a difference.  There are
four:  Adaptive
Server Enterprise (ASE), Adaptive Server Anywhere (ASA), Sybase IQ
(IQ), and
Advantage Database Server.
However, in general (and perhaps assuming ASE, which is what I'm most
familiar with)
One method is to select the results into a temp table adding a
rownumber field,
you can then
"select ... from temptable where rownum between ((pageno -1) * 10) +
1    and (pageno * 10) "
More details athttp://www.isug.com/Sybase_FAQ/ASE/section6.2.html#6.2.12
If using ASE 15.x, you might also consider using a scrollable cursor
to accommodate paging
backwards and forwards through the result set.
http://infocenter.sybase.com/help/topic/com.sybase.help.ase_15.0.sqlu...
Ok thank you Mr. Bret_Halford, im using ASE 15 and your suggestion its
very well, this is the code working, it might be useful for somebody,
thanks:

set nocount on
select
rownum = identity(9),
A.PMM_DATETIME as PMM_DATETIME
into #foobar
from RF as A
left join CAT_SITIOS as B
on A.Region = B.Region
and A.SID = B.SID
and A.NID = B.NID
and A.Cell_Id = B.Cell_Id
where
A.PMM_DATETIME >= '2010-02-03 00:00:00' and A.PMM_DATETIME <=
'2010-02-03 23:59:59'
and A.Region = 0
order by A.PMM_DATETIME

-- This is the query for paging:
select * from #foobar
where rownum > 20 and rownum <= 30

drop table #foobar
set nocount off

Best Regards

Loading...