1 Note that if it is only for a display purpose show row numbers in the front end application

2 You can use a temporary table with identity column

SELECT identity(int,1,1) as row_num,* from #temp from your_table

SELECT * from #temp order by Row_num

Thanks for your reply.

but i want to generate row_numbers as in SQL 2005 as i have some calculation to do on data like i want to get the records from row numer=5.

I heard about corelated queries but i am not able to figure it how?

Do you have a primary/unique key?

Similar to the previous answerer..

SELECT  Identity(1,1)  as RowNumer,  *
INTO  #tmpTable
FROM yourtablename

Then work with the #tmpTable for ur calculations

I couldn't get this to run on SQL2000

-- Generate row numbers using a triangle join; must have a primary key and select and group by all columns

select count(*) as RowNum, PrimaryKeyColumn, OtherColumn1, OtherColumn2, OtherColumnN

from Table1 a

join Table1 b on a.PrimaryKeyColumn >= b.PrimaryKeyColumn

group by PrimaryKeyColumn, OtherColumn1, OtherColumn2, OtherColumnN

order by RowNum

Oops, sorry...forgot my aliases.

-- Generate row numbers using a triangle join; must have a primary key and select and group by all columns

select count(*) as RowNum, a.PrimaryKeyColumn, a.OtherColumn1, a.OtherColumn2, a.OtherColumnN

from Table1 a

join Table1 b on a.PrimaryKeyColumn >= b.PrimaryKeyColumn

group by a.PrimaryKeyColumn, a.OtherColumn1, a.OtherColumn2, a.OtherColumnN

order by RowNum