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:

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:

And at page 2 I'd have:

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

CodePudding user response：

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:

Page 2: