SqlServer: return range of records

Problem:

How to get top 10 records then records from 11-20 then 21-30

The scenario is like, if the index from front end is given, then it should return records accordingly,

Index   rows

1              1-10

2              11-20

3              21-30

Solution:

declare @index int

set @index=2

;with cte

as

(select *, ROW_NUMBER() over(order by BusinessEntityId )as row_num from HumanResources.Employee

)

select * from cte where row_num>=(@index*10-9) and row_num<=(@index*10)

In the above solution, I have applied this on table ‘HumanResources.Employee’ of AdventureWorks2008 database. It returns records for index 2 thus returns records from 11-20

We can make this solution generic,

select *, ROW_NUMBER() over(order by [Column_NameForOrderBy] )as row_num from [Table_Name]

Please provide your feedback for the post, if you find this post useful.