Retrieve nth row from a table in SQL Server

Retrieve nth row from a table in SQL Server

In below example I am going to explain in two way. For below explanation I have taken n=6.

Method: 1

WITH Records AS

(

select *,row_number() OVER(order by empid) as TempEmployee from employee

) 

select * from records where TempEmployee=5 

We are creating a CTE and selecting all the columns including an extra column which is going to be generated using ROW_NUMBER( ) inbuilt function, which will generate row number for each row.

After that we are selecting the required row from the created CTE

Method: 2 

SELECT * FROM (SELECT *, row_number() OVER(order by empid) AS RowNum from employee) Resultset

WHERE RowNum = 5

In above query, the inner query will give all the columns including an extra column using ROW_NUMBER ( ) function and later as part of outer query we are filtering using WHERE clause for required row number.