Hi, i'm new to the boards...and somewhat new to Sybase (come from an oracle/mysql background). Anyways, one of the concepts that I see hard to grasp when building a web based front end for my Sybase ASE 12.5.3 database is pagination.

This is when records are split into a list of pages that users can view at one at a time. With oracle I used a combination of functions such as START WITH etc. to do the job. With MySql you can use LIMIT (startrowindex, endrowindex).

Is there an equivalence in Sybase? I have done exhaustive searching on this board and have found suggestions with set record count but this is hard to implement as I don't know what I would use to tell Sybase to start with which particualr row number on the next page (as is the case in ORDER BY queries that don't necesseraliy return the primary key in sequential order)

please help

anyone ?

There are several suggestions that I have seen before. One of them is to select the result set into a temp table with an indentity column, and then use that as your "rownumber" in subsequent queries:

ie:

select *,rowid = identity(10)
into #mylist
from sysobjects
where <my criteria>

select * from #mylist where rowid between 1 and 50
select * from #mylist where rowid between 51 and 100
etc.

http://www.isug.com/Sybase_FAQ/ASE/s....2.html#6.2.12

Shows my stored procedure trick (as learned from John McVicker).

ok, thanks guys...both methods look promising.

so it looks like the first method (KSherlock's) builds a temp table with a pseduo-key called rowid. Then, it querys that temp table where the identity column matches the range of the first and last records. OK..couple questions:

1. What is the lifespan of the temp table? is it only for the life of the procedure?
2. How would it perform if loaded with 15,000 records?
3. Are temp tables joinable with physical tables?

the second method (MattR's), i've seen before while searching google. it looks like the ideal smart solution...however, I have trouble grasping what @min_postid exactly does...could you please explain its role?

Thanks,
Mike

Well, the code was for a forum system like this, so you only want to display a certain subset of posts on a given page. PostID, in this case, is an IDENTITY column although it could be any sort of sortable column.

You first determine how many posts you want per page then calculate how many rows you need to offset (posts per page * page number to display). You then iterate through all of your posts to get the postID which is the first post to be displayed. This is @min_postid. You can then say "Show me all posts greater/equal to @min_postID and stop after #per_page of them).

thanks for the reply MattR.

Everything makes sense to me except exactly what is happening here:
(It could be due to my lack of experience)
What does this exactly do/return? Does this create a variable called min_postid with the value being the table identity postid? Would that be the record with the highest value of postid? Sorry for my ignorance, I just haven't seen this syntax used before...i'm not quite sure what its doing.

Thanks,
Mike

As it says in the FAQ warning:
.

Basically if your table consists of the IDs:
1
2
3
4
5
6

when that SQL is done with the value of 6 will be stuck in the variable.

Hi,
The stored procedure solution for pagination is not generic. We have to write individual stored procedures for each table.

Is there any other solution for this