CODEWITHSUNDEEP
sybasesap-ase
antosnowin
antosnowin

Reputation: 221

how to set row number in sybase query

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

Upvotes: 4

Views: 45444

Answers (2)

Orhan Celik
Orhan Celik

Reputation: 1575

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

Upvotes: 2

Robert
Robert

Reputation: 25753

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

Upvotes: 8

Related Questions