RowNum() Equivalent in SQL 2000+
Oracle has a pseudo-column called ROWNUM that allows you to display the row number for each row. There are infinite reasons why you might want to use the ROWNUM, but in SQL it’s not nearly as easy to determine.
In Oracle, it’s simply
SELECT ROWNUM from table_name
In SQL 2005+, there is actually a ROW_NUMBER() function, but you have to give it some more information to use it.
ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
For example:
use [Database]
select row_number() over (order by data_srce_id desc) rownum
,data_srce_id
from data_srce
|
rownum |
data_srce_id |
|
1 |
8 |
|
2 |
7 |
|
3 |
6 |
|
4 |
5 |
|
5 |
4 |
|
6 |
3 |
|
7 |
2 |
|
8 |
1 |
|
9 |
-1 |
However, in SQL 2000 it’s a little more difficult. The only way I’ve found is using the IDENTITY() function, which requires an INTO statement, so you pretty much have to load the results into a #temporary table and then select from the #temporary table like this:
use [Database]
SELECT IDENTITY(int, 1,1) id
,t1.[column1] col1
,t1.[column2] col2
,t1.[column3] col3
,t2.[column2] col4
INTO #Temp
FROM [dbo].[Table] t1
INNER JOIN [dbo].[Table2] t2
ON t.[column] = t2.[column]
select *
from #Temp
Recent Buzz