how to set row number in sybase query

Question

I have a select statemnt which will return results say 1000 using join with some 3 to 4 tables. My requirement is to have identity column in the resultset . Can anyone help me on this.

ex :

Result :
id   name 
--   ----
001   xxx
002   yyy
003   zzz

My requirment :
Rowid   id   name 
 1      --   ----
 2       001   xxx 
 3       002   yyy
 4       003   zzz

Like Row_number in sql , do we have anything here in sybase

score 8 · Accepted Answer · answered May 21 '13 at 06:52

8

In sybase there isn't row_number or something like that. Maybe temporary tables with identity column will help you?

Consider below example please.

 select Rowid = identity(8), id, name      
 into #temtab
 from tab

 select Rowid, id, name
 from #temtab

answered May 21 '13 at 06:52

Robert

25,425
8
67
81

what do you think of my answers for future developer ? – Moudiz Dec 28 '15 at 08:10
@Moudiz It would be harder for more complicated tables structure. – Robert Dec 28 '15 at 13:16
do you recomand to remove it or keep it with more explanation ? – Moudiz Dec 28 '15 at 13:58
@Moudiz In my opinion it should be removed. Your answer doesn't solve OP's problem. – Robert Dec 29 '15 at 20:24

score 2 · Answer 2 · answered Dec 22 '16 at 08:24

2

In the version of Sybase IQ I use, version Number() or ROW_NUMBER() functions exist. In my version you may use following:

select Number() rowid, id, name      
 from tab

or

select ROW_NUMBER() OVER (PARTITION BY name ORDER BY id) rowid, id, name      
 from tab

or

select ROW_NUMBER() OVER (ORDER BY id) rowid, id, name      
 from tab

answered Dec 22 '16 at 08:24

Orhan Celik

1,513
15
12

But what if you don't want to order by ROW_NUMBER()? – user8128167 Jan 25 '19 at 14:55

how to set row number in sybase query

2 Answers2

Linked