ROW_NUMBER function using Sql server

ROW_NUMBER using Sql server

Using Row_number function we can get a listing of table with a sequential list.

select ROW_NUMBER() over (order by s_Acc_id) as RowNumber, s_Acc_id from Customer.ShippingAddress

Output is

1    kingslin
2    Bala
3    Rajesh
4    varakulan
5    stalin
6    stalin
7    stalin
8    stalin
9    vasanth
10    vasanth



Using this Row_number function, we can find out particular row.

Example :

You are running a sql query , you need 2nd row of that result,
Using CTE and Row_number function , we can get particular row

Example Query:

WITH OrderedOrders AS

(select ROW_NUMBER() over (order by s_Acc_id) as RowNumber, s_Acc_id from Customer.ShippingAddress

)

SELECT *

FROM OrderedOrders

WHERE RowNumber=2

Output is
2    Bala