Hi, I am trying to think of a solution to this problem.
Let's say I have 10,000 records in my database and my web application displays the records in pages ( 100 records per page ). Let's say I would like to navigate to page 3, how can I write a stored procedure for Sybase ASE to return the correct 100 records to me ?
I can achieve this in MS SQL Server as it supports SELECT TOP 100, but I don't think there is an equivalent to SELECT TOP in sybase, is there ?
I hope someone can give me some pointers, Thanks !
you can try a variant of the "top ten" query, but i cannot guarantee that it will be efficient on a table of 10,000 records
select column1, column2, column3
from yourTable ZZ
where ( select count(*)
where column1 > ZZ.column1 )
between 301 and 400
order by column1 descending
with a web application, you may have some scripting at your disposal (php? coldfusion?) and another approach might be to store the page marker values in an array -- but this means processing the entire table at least once (and again if inserts are not at the bottom)
Thanks for your input, rudy. I've tested your method and realised that it does not work for me as I have duplicate values for column1.
I have tried another method which is less ideal but seems to get me the results I wanted.
TO GET ROWS 50 - 100 :
1) Use SET ROWCOUNT 100; then run a select statement to get the first 100 rows and put it in temporary table A.
2) Reverse the rows in table A by running a select statement order by column1 desc; then run a select statement to get the first 50 rows and put it in temporary table B.
3) Run a select statement on table B order by column1.
Thanks again for your help !
Provided you have a primary key, do this:
If you want rows 75 through 100
DECLARE @somepk INT
SET ROWCOUNT 75
-- This will give you your 75th primary key
SELECT @somepk = primarykey
ORDER BY primarykey ASC
SET ROWCOUNT 25
WHERE primarykey > @primarykey
ORDER BY primarykey ASC --, and other stuff