How to get nth record form table?

If we are filling result to any data-source then it is easy to get nth record from that data-source, by passing row index we can access nth record, but for this we have to perform two steps.

1. First we have to fill data source
2. Based on row index we have to get data from data-source.

Instead of these steps, we can do this directly in database side only, for this we have to use ROW_NUMBER () in query. This function include one sequence number for each record in result set. For this first we have to include ORDER BY clause in query because based on that only ROW_NUMBER() function include one sequential number for record. 

Syntax-

select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =index

Here index is nth row; we have to replace with any number.

Here is example-
 
Select *
from (select ROW_NUMBER()  over (order by columnname) as rownum from tablename) t1
where t1.rownum =2

This query will return 2nd row from table. Result set contains sequence number based on ORDER BY clause which has performed over given column. Sequence number can be different if we are passing different column name for ordering. We can also put where condition in inner query.


Thanks