Paging by groups of records instead of rows

Question

I have a query that returns the following:

ID Rank 1 1 2 1 3 2 4 3 5 4 6 4

I am trying to use the OFFSET / FETCH NEXT at the end to return the records paged

I.E. if I only want 3 rows per page and I want page 1, I would pass a 1 to @Page, and 3 to @PageRecordCount below, and for page 2, a @Page = 2 to get page 2 and so on...

OFFSET (@Page - 1) * @PageRecordCount ROWS FETCH NEXT @PageRecordCount ROWS ONLY;

If I need the full grouping of records for each page, how can I accomplish this?

What I am expecting is as follows: @Page = 1 @PageRecordCount = 3

Return:

ID 1 2 3 4

I want to return the number of groupings, rather than the number of rows. Is this possible?

Huh? You want page 1 with 3 records on it to give you 4 records back? Can you please clarify? — Becuzz
– Becuzz, Commented Oct 28, 2015 at 21:34
@Becuzz - I want it to return the number of Ranks rather than rows. So you pass in 3 and get rank 1-3 back. — mameesh
– mameesh, Commented Oct 28, 2015 at 21:35

Becuzz · Accepted Answer · 2015-10-29 13:04:25Z

3

I think you want this:

;with cte as(your current query) select * from cte where rank > (@page - 1) * @count and rank <= @page * @count

edited Oct 29, 2015 at 13:04

Becuzz

6,86728 silver badges39 bronze badges

answered Oct 28, 2015 at 21:38

Giorgi Nakeuri

35.9k8 gold badges50 silver badges78 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Paging by groups of records instead of rows

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related