Sybase add incrementing counter to a select statement

Question

It seems that a similar question has been asked an solutions exist for other DB products (especially MS-SQL) but they don't work for sybase so I'm asking this question.

I have a simple select statement and I'd like to get a column containing and incrementing counter along with the results.

eg:

counter  data1  data2
0        aa     AA
1        bb     BB
2        cc     CC

Is there a way to do this in a single statement as opposed to putting it into a temp table and running a cursor over it?

how many results in total (usually)? – aF. Jan 06 '12 at 17:50 — aF., Jan 06 '12 at 17:50
Not many, around 100, definitely within an int :) – Victor Parmar Jan 06 '12 at 18:16 — Victor Parmar, Jan 06 '12 at 18:16

score 3 · Accepted Answer · answered Jan 09 '12 at 09:43

3

You need as well to create a temp table but you don't need any cursor, just do it like this:

select counter = identity(10), data1, data2 into #t1 from tablename

select * from #t1
drop table #t1

answered Jan 09 '12 at 09:43

aF.

64,980
43
135
198

Nice - this is what I was looking for!!! Now is there any way to specify what number to start at? – Victor Parmar Jan 09 '12 at 22:04
@VictorParmar: You can always add an offset in the final SELECT, i.e. `SELECT counter + 10 AS counter, data1, ... FROM #t1`. – Andriy M Jan 09 '12 at 22:12

score 3 · Answer 2 · answered Jan 09 '12 at 22:10

3

Depending on the version of Sybase you are using, you could also have a look at functions like NUMBER() or ROW_NUMBER().

answered Jan 09 '12 at 22:10

Andriy M

76,112
17
94
154

Sybase add incrementing counter to a select statement

2 Answers2