Way way back before ROW_NUMBER in SQL Server 2005 was introduced, people had a hard time looking for a solution on how to display row numbers in the queries without using temp tables.
The trick is to have a subquery where you can compare a column in the order clause against a certain column in your main SQL. Below is the sample select statement:
1
2 use Northwind
3
4 select (
5 select SUM(1)
6 from Region reg1
7 where
8 reg1.RegionID <= reg2.RegionID
9 ) as 'Row Number',
10 *
11 from Region reg2
12
The trick I was talking about is this: reg1.RegionID <= reg2.RegionID
You can also use this trick for getting the running total of a certain column. This is really a neat trick but of course there is also a downside to this solution. Using an ORDER BY clause is one of its limitation.