Increment a number in a select statement

NedKelly

Hi im stuck in this

i need to increment a number in a select statement

i cant use an autonum as i dont want to change the database table structure

ie



select field 1 , field 2 , inremented_field from table

should result in

field 1 field 2 0
field 1 field 2 1
field 1 field 2 2
field 1 field 2 3

any ideas ?

stevenmu

Try

SELECT field1, field2, ROW_NUMBER() OVER(ORDER BY field1) as incremented_field
FROM table

That'll work in SQL Server at least.

NedKelly

tried that in sql server 2000 and i get

Server: Msg 195, Level 15, State 10, Line 1
'ROW_NUMBER' is not a recognized function name.

kayos

SELECT ROWID=IDENTITY(int,1,1) , * 
INTO #tmp FROM table ORDER BY Field1

SELECT * FROM #tmp

That should work in 2000

stevenmu

Ah yes, it was only introduced in 2005. Not very efficient but can you create a temp table with an identity column and select into that ?

showry

You could do something like this provided you were ordering by a unique key

SELECT name,
(
SELECT COUNT( * )
FROM t_test t2
WHERE t2.id < t1.id
)
FROM t_test t1
ORDER BY id

mountainyman

declare @rowNumber
then set a cursor MyCursor
Then

set @RowNum = 0 

	WHILE @@FETCH_STATUS = 0
	BEGIN
	  set @RowNum = @RowNum + 1

	  FETCH NEXT FROM MyCursor