Results 1 to 9 of 9
  1. #1
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb Unanswered: Sybase's ROWNUM function

    Hi i have an Oracle query like

    SELECT rownum,i from t
    (rownum is a key word not column name)
    this will return two columns one 'rownum' with the value same as the row number. and the other column value i.

    What should i do to get the same result in SYBASE

  2. #2
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Hi,
    There is no function like rownum or rowid in sybase. if you want to get the desired output using sybase. u have to use it as below in sybase

    select identity(10) as "rownum", i into #temp from t
    select * from #temp
    drop table #temp
    go

    u have to use temporary table(#temp) for this purpose

  3. #3
    Join Date
    Oct 2006
    Posts
    83
    Thanks,
    Could u please explain it clearly.

  4. #4
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    select identity(10) as "rownum", i into #temp from t
    select * from #temp
    drop table #temp
    go

    1. You cant do it in a single query as it can be done in oracle. there is no functions like rownum or rowid (inbulit functions) in sybase
    2. Inorder to do the same in sybase, you need to do the above said steps to get the outputs mentioned (i.e) you mean to append the serial number for each row in the table t
    3. In sybase u can copy the records from one table to another by using select...into. There is no need of having ddl created for the table to which records have to be copied, in this case the table is #temp (# means temporary table stored in tempdb database). It has the same columns as in table t with new column created as rownum which of type identity
    4. The identity column by default starts from 1 and automatically increaments by 1 whenever an insert happens using select..into
    5. Example : table : t
    select * from t
    i
    -
    100
    200
    300

    after executing the below
    select identity(10) as "rownum", i into #temp from t
    select * from #temp
    drop #temp
    go

    the result will be
    rownum i
    ------- -
    1 100
    2 200
    3 300

  5. #5
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Quote Originally Posted by parangiri
    select identity(10) as "rownum", i into #temp from t
    select * from #temp
    drop table #temp
    go

    1. You cant do it in a single query as it can be done in oracle. there is no functions like rownum or rowid (inbulit functions) in sybase
    2. Inorder to do the same in sybase, you need to do the above said steps to get the outputs mentioned (i.e) you mean to append the serial number for each row in the table t
    3. In sybase u can copy the records from one table to another by using select...into. There is no need of having ddl created for the table to which records have to be copied, in this case the table is #temp (# means temporary table stored in tempdb database). It has the same columns as in table t with new column created as rownum which of type identity
    4. The identity column by default starts from 1 and automatically increaments by 1 whenever an insert happens using select..into
    5. Example : table : t
    select * from t
    i
    -
    100
    200
    300

    after executing the below
    select identity(10) as "rownum", i into #temp from t
    select * from #temp
    drop #temp
    go

    the result will be
    rownum i
    ------- -
    1 100
    2 200
    3 300
    It seems in few version the above is not supported, so u can try with

    select rownum = identity(10) , i into #temp from t
    select * from #temp
    drop #temp
    go

  6. #6
    Join Date
    Oct 2006
    Posts
    83

    Lightbulb

    Thanks parangiri ,

    SELECT ROWNUM=identity(10), id,name
    INTO #tmp
    FROM emp
    SELECT * from #tmp


    I tried the above query in Sybase IQ 12.7 . but it didn't execute, it throws the following error

    Function not currently supported. --at Query command line :1
    --
    (do_Heap.cxx 4603)
    SQLCODE=-1001025, ODBC 3 State"HY000"
    Line 1, column 1

    PLease sort me out .

    will the below query works in Sybase IQ 12.7

    select identity(10) as "rownum", i into #temp from t
    select * from #temp


    Thanks,
    reddy

  7. #7
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    hi,
    i looked into this in the morning. i am not sure whether select..into works in sybase 12.7. But the select..into works in 12.5 (which i am using) so i feel this might be the problem. but not sure. i am not sure about the error you are getting. can u just try a sample code using select..into in sybase 12.7 so that we can come to a conclusion. This was something different.

    select identity(10) as "rownum", i into #temp from t
    select * from #temp does not work
    Last edited by parangiri; 03-27-07 at 09:11.

  8. #8
    Join Date
    Feb 2002
    Location
    Willy is on vacation
    Posts
    1,208
    The equivalent to Oracle rownum is ROWID(<TABLENAME>).

    SELECT SALESFACT.ID, ROWID ( SALESFACT)
    FROM SALESFACT
    WHERE ROWID ( SALESFACT) < 400

  9. #9
    Join Date
    Feb 2007
    Location
    Chennai, TN
    Posts
    68
    Yes. U r right. it seems ROWID can be used in IQ version (i.e) sybase 12.7. but it does not work in adaptive server enterprise (i.e) 12.5.. Thanks for the information.

Posting Permissions

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