How to create RowNum column in SQL Server?

Question

In Oracle we have "rownum". What can I do in SQL Server?

Answer 1

In SQL Server 2005 (and 2008) you can use the ROW_NUMBER function, coupled with the OVER clause to determine the order in which the rows should be counted.

Update

Hmm. I don't actually know what the Oracle version does. If it's giving you a unique number per row (across the entire table), then I'm not sure there's a way to do that in SQL Server. SQL Server's ROW_NUMBER() only works for the rows returned in the current query.

Answer 2

If you have an id column, you can do this:

select a.*, 
(select count(*) from mytable b where b.id <= a.id) as rownum
from mytable a
order by id;

Of course, this only works where you're able to order rownums in the same (or opposite) order as the order of the ids.

If you're selecting a proper subset of rows, of course you need to apply the same predicate to the whole select and to the subquery:

select a.*, 
(select count(*) from table b where b.id <= a.id and b.foo = 'X') as rownum
from table a where a.foo = 'X'
order by id;

Obviously, this is not particularly efficient.

Answer 3

Based on my understanding, you'd need to use ranking functions and/or the TOP clause. The SQL Server features are specific, the Oracle one combines the 2 concepts.

The ranking function is simple: here is why you'd use TOP. Note: you can't WHERE on ROWNUMBER directly...

'Orable:
select 
  column_1, column_2 
from 
  table_1, table_2
where
  field_3 = 'some value'
and rownum < 5

--MSSQL:
select top 4
  column_1, column_2 
from 
  table_1, table_2
where
  field_3 = 'some value'