Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2002

    Unanswered: grab middle records

    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 !

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    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(*)
    from yourTable
    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)


  3. #3
    Join Date
    Jun 2002
    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 !

  4. #4
    Join Date
    Mar 2001
    Lexington, KY
    Bluebytez this is how I do it:

    Provided you have a primary key, do this:
    If you want rows 75 through 100

    DECLARE @somepk INT
    -- This will give you your 75th primary key
    SELECT @somepk = primarykey
      FROM mytable
     ORDER BY primarykey ASC
    SELECT *
      FROM mytable
     WHERE primarykey > @primarykey
     ORDER BY primarykey ASC --, and other stuff


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts