Implementing the RowNumber of Oralce database in SQL Server

Referenced from: http://cai555.javaeye.com/blog/466033

Method 1:

With temp as

(select Row_number () over (order by Cityid) as Rownum,cityname from city)

SELECT * from temp where rownum between and 20

Go

Explanation :

1 This method places the result of the query in parentheses into the variable : Temp (I'm not sure if it's a variable), a line number trace with the Row_number () function , and then a column collation with the over function ( is required ) and specifies that the column is named ' rownum '

2 The following statement can be followed by a query that specifies the line number for ' rownum '

3 after the execution of this batch of statements, the variable : Temp release

Method 2:

Select Identity (int,1,1) as rownum, CityName into #temp from city

SELECT * from #temp where rownum between and 20

Go

Explanation :

This method has the same meaning as above , except that the row_number () function is replaced with the identity () function

and put the result set in a temporary table , when the batch statement is completed , this temporary table can also be used