Paging by groups of records instead of rows

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

  • SQL Server Get values of top three records and display in one row per person
  • Best practice for handling SQL injections when calling a stored procedure
  • SQL Numeric data type truncating value?
  • MSSQL & SQL data type alternatives?
  • SQL There is already an object named '#TEMP' in the database after updated to SAP PL-16
  • Slow “Select” Query with varchar(max)
  • 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?

    One Solution collect form web for “Paging by groups of records instead of rows”

    I think you want this:

    ;with cte as(your current query)
    select * from cte
    where rank > (@page - 1) * @count and rank <= @page * @count
    
    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.