How to Implement Pagination with OFFSET and FETCH or ROW_NUMBER in SQL Server?

SQL paging implementation: using SKIP and TAKE keywords

Problem Description

You are processing a query that retrieves recently discussed forum posts based on the most recent entry for each post. You want to extend this query to support pagination, specifically to allow the user to retrieve a specific range of posts, such as "the 10th to 20th most recently active posts".

Answer

SQL Server 2012 and above

In SQL Server 2012 and later, you can easily implement paging using the OFFSET and FETCH keywords:

SELECT PostId
FROM (
    SELECT PostId, MAX(Datemade) AS LastDate
    FROM dbForumEntry
    GROUP BY PostId
) SubQueryAlias
ORDER BY LastDate DESC
OFFSET 10 ROWS
FETCH NEXT 10 ROWS ONLY;

This query skips the first 10 rows (offset) and gets the next 10 rows.

SQL Server 2008

In SQL Server 2008, you can use the following techniques:

DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 10, @End = 20;

WITH PostCTE AS (
    SELECT PostId, MAX(Datemade) AS LastDate
        ,ROW_NUMBER() OVER (ORDER BY PostId) AS RowNumber
    FROM dbForumEntry
    GROUP BY PostId
)
SELECT PostId, LastDate
FROM PostCTE
WHERE RowNumber BETWEEN @Start + 1 AND @End
ORDER BY RowNumber;

This query uses a common table expression (CTE) to generate row numbers for each post. The WHERE clause then filters the results to include only posts within the specified range, and the ORDER BY clause ensures that posts are returned in increasing order by PostId.

The above is the detailed content of How to Implement Pagination with OFFSET and FETCH or ROW_NUMBER in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!