views:

2193

answers:

3

Hi guys

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...

Thanks in advance.

--EDIT Sorry, forgot to mention, must run on SQL Server 2000

A: 

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
CodeMonkey1
Sorry, I forgot to mention, must run over SQL 2000
Rodrigo
+6  A: 

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

SQLMenace
Don't forget to drop the temp table
Joel Coehoorn
A: 

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.

JohnFx
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
SQLMenace
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.
JohnFx
The scenario is: to select several thousand rows from a table :_(
Rodrigo