Pagination with grouping

I have a table with over 800K records. This table needs to be “grouped by” a certain column. An example would be:

However, with pagination, things get complicated. I want to show 50 rows for every group.

This means that if I have the following data:

Id	Color	Name
1	Red	Paper #1
2	Red	Paper #2
3	Red	Paper #3
4	Green	Paper #4
5	Green	Paper #5
6	Green	Paper #6
7	Green	Paper #7

And I wanted to get 2 items for every group per page (instead of 50, for the sake of this example), on page 1 I’d have:

Id	Color	Name
1	Red	Paper #1
2	Red	Paper #2
4	Green	Paper #4
5	Green	Paper #5

And at page 2 I’d have:

Id	Color	Name
3	Red	Paper #3
6	Green	Paper #6
7	Green	Paper #7

My current SQL query (SQL Server) for pagination is the following:

SELECT
   * 
from
   [Order] 
ORDER BY
[From] ASC
OFFSET (@PageNumber - 1) * @RowsOfPage ROWS FETCH NEXT @RowsOfPage ROWS ONLY

Answer

Using this sample data:

CREATE TABLE Orders(Id int, Color varchar(10), Name varchar(50));
INSERT INTO Orders(Id,Color,Name) VALUES 
  (1, 'Red', 'Paper #1'),
  (2, 'Red', 'Paper #2'),
  (3, 'Red', 'Paper #3'),
  (4, 'Green', 'Paper #4'),
  (5, 'Green', 'Paper #5'),
  (6, 'Green', 'Paper #6'),
  (7, 'Green', 'Paper #7');

This query will do:

DECLARE @PageNum int = 1;
DECLARE @PageSize int = 2;
SELECT *
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY Color ORDER BY Name) AS Row
  FROM Orders
) t1
WHERE Row BETWEEN ((@PageNum-1)*@PageSize+1) AND (@PageNum*@PageSize)
ORDER BY Color,Name

Page 1: http://sqlfiddle.com/#!18/748cc2/27/0

Page 2: http://sqlfiddle.com/#!18/748cc2/25/0