Incremental Counter

[Scotty2]

Hi.
My table is in the order I want it to be, but I need to update a spare field in each row with an incremental counter, so that I can do an "order by".

ie Field1 Field2 Spare Field
Row 1 AAA 02 17 2001 Set value to 1
Row 2 XXX 02 12 2001 Set Value to 2
Row 3 MMM 30 01 2001 Set Value to 3
Row 4 BBB 14 02 2001 Set value to 4
Row 5 and so on
In fact, the counter can be ascending or descending, and could even be the exact time of the update, just as long as I have a true sequence.
Best Wishes
Scotty
We have Sybase 11.5

 

[Tim1]

Hi Scotty,

Why don't you make the column a sequenced column? There should be a FAQ on making sequenced columns, or you could check

http://www.sybase.com.

Tim

 

[Scotty2]

Tim
I am doing my processing from within a Stored Procedure, using SELECT bla bla INTO Temporary Table from bla bla.
Can "Sequenced Columns" be set-up for a Temporary Table's column, within such a scenario ?
I have Sybase Books ONLINE, & SQL Server 11 .0 Generic / Reference Manual doesn't refer to them at all.
Many thanks for trying to help me, but further suggestions would be greatly appreciated.
Scotty

 

[Tim1]

Hiya,

No, I don't think that you can define a sequential column on a temp table, I have never seen one anyway.

In that case, why don't use use the logic

spare field = (SELECT MAX(spare_field))+1

Tim

 

[Scotty2]

Hi Tim1
My temporary table is "#x_temp".
My Spare Field is "counter", set to zeros on creation.
If I write : -
update #x_temp
set counter = (select max(t2.counter) + 1 from #x_temp t2)
then all the rows have counter set to 1.
If I leave out the 2nd reference to #x_temp, I get the syntax error : Invalid Column Name Counter.
Am I doing something wrong ?
Best Wishes
Scotty