MSSQL Select statement with incremental integer column... not from a table
For SQL 2005 and up
SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',* FROM YourTablefor 2000 you need to do something like this
SELECT IDENTITY(INT, 1,1) AS Rank ,VALUEINTO #Ranks FROM YourTable WHERE 1=0INSERT INTO #RanksSELECT SomeColumn FROM YourTableORDER BY SomeColumn SELECT * FROM #RanksOrder By Rankssee also here Row Number
You can start with a custom number and increment from there, for example you want to add a cheque number for each payment you can do:
select @StartChequeNumber = 3446;SELECT ((ROW_NUMBER() OVER(ORDER BY AnyColumn)) + @StartChequeNumber ) AS 'ChequeNumber',* FROM YourTablewill give the correct cheque number for each row.
Try ROW_NUMBER()
http://msdn.microsoft.com/en-us/library/ms186734.aspx
Example:
SELECT col1, col2, ROW_NUMBER() OVER (ORDER BY col1) AS rownumFROM tbl