Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: selecting 1 row at a time in Sybase

    Hello all,

    I am in the process of writing an interface to an application running on sybase. What I need to do, is to select an entire table, but only recieve 1 row at a time. I have done this in Oracle using the following statement :-

    SELECT * FROM (SELECT F1, F2, F3, ROW_NUMBER() OVER (ORDER BY F1) x FROM TABLE1) WHERE x=number

    and this query gets called with number incrementing till it reaches the end.

    I have searched, and been unable to find a function that resembles ROW_NUMBER() in sybase. Does such a function exist?

    Many thanks in advance
    Scott Cheadle.

  2. #2
    Join Date
    Jan 2003
    Location
    Geneva, Switzerland
    Posts
    353
    Sybase does not have the concept of the row number, and so doesn't have anything like a ROW_NUMBER() function.

    If you need to fetch one row at a time you could open a cursor.
    Otherwise you need to figure out how to identify the next row in the sequence in some way and fetch it with the appropriate WHERE clause.
    Note that the "next" row really depends on the ORDER BY clause - there is no guarantee that the rows will come back in any particular order if you don't specify an ORDER BY.

    Michael

  3. #3
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    Actually, Sybase did try to imlpement the ROW_ID in a really crude fashion.

    sp_dboption 'db name', 'auto identiry', 'true

    This is usually done at the initial database creation time and all the tables created after thsi setting would automatically have a SYB_IDENTITY_COL column, which pretty much behaves like an normal identity column.

    This wasn't terribly sucessful because of the identity gaps that can occur, for exxample the row with SYB_IDENTITY_COL= 200000 may in fact be the 100000th record

Posting Permissions

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