Friday, June 14, 2013

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.


No comments:

Post a Comment