MSSQL Select statement with incremental integer column... not from a table

Question

I need, if possible, a t-sql query that, returning the values from an arbitrary table, also returns a incremental integer column with value = 1 for the first row, 2 for the second, and so on.

This column does not actually resides in any table, and must be strictly incremental, because the ORDER BY clause could sort the rows of the table and I want the incremental row in perfect shape always.

The solution must run on SQL Server 2000

Chavira

Answer 1

For SQL 2005 and up

SELECT ROW_NUMBER() OVER( ORDER BY SomeColumn ) AS 'rownumber',*
    FROM YourTable

for 2000 you need to do something like this

SELECT IDENTITY(INT, 1,1) AS Rank ,VALUE
INTO #Ranks FROM YourTable WHERE 1=0

INSERT INTO #Ranks
SELECT SomeColumn  FROM YourTable
ORDER BY SomeColumn 

SELECT * FROM #Ranks
Order By Ranks

see also here Row Number

Nor

Don't forget to drop the temp table – Potiche

Answer 2

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 YourTable

will give the correct cheque number for each row.

Benefic

Answer 3

Try ROW_NUMBER()

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Example:

SELECT
  col1,
  col2,
  ROW_NUMBER() OVER (ORDER BY col1) AS rownum
FROM tbl

Occasional

Sorry, I forgot to mention, must run over SQL 2000 – Chavira

Answer 4

It is ugly and performs badly, but technically this works on any table with at least one unique field AND works in SQL 2000.

SELECT (SELECT COUNT(*) FROM myTable T1 WHERE T1.UniqueField<=T2.UniqueField) as RowNum, T2.OtherField
FROM myTable T2
ORDER By T2.UniqueField

Note: If you use this approach and add a WHERE clause to the outer SELECT, you have to added it to the inner SELECT also if you want the numbers to be continuous.

Fiume

That will work but like you already mentioned for every row it has to scan the whole table, this is also known as a running count – Nor

Yeah, it is a majorly sucky solution. However, under the constraint of SQL2K it is either this or the temp table (already offered in another answer). I suppose it depends on the exact scenario which solution is the lesser evil. – Fiume

The scenario is: to select several thousand rows from a table :_( – Chavira