How to Implement Query Paging in SQL Server Using OFFSET and FETCH NEXT, and Alternatives for Older Versions?

Use SQL's OFFSET and FETCH NEXT to implement paging queries (SQL Server 2012 and above)

This example demonstrates how to add pagination to a query to retrieve the most recently discussed forum posts. The goal is to limit the results to a specified range of rows (for example, "Items 10 to 20").

SQL Server 2012 introduced the OFFSET and FETCH NEXT keywords to simplify the implementation of paging:

<code class="language-sql">SELECT col1, col2, ...
FROM ...
WHERE ...
ORDER BY -- 必须包含 ORDER BY 语句
-- 分页语句
OFFSET 10 ROWS -- 跳过 10 行
FETCH NEXT 10 ROWS ONLY; -- 获取 10 行</code>

Modify original query:

<code class="language-sql">SELECT PostId
FROM
(
    SELECT PostId, MAX(Datemade) AS LastDate
    FROM dbForumEntry
    GROUP BY PostId
) AS SubQueryAlias
ORDER BY LastDate DESC
OFFSET 10 ROWS -- 跳过 10 行
FETCH NEXT 10 ROWS ONLY; -- 获取 10 行</code>

Pagination in SQL Server 2008 and earlier

In versions prior to SQL Server 2012, you can use the ROW_NUMBER() and COUNT() functions in combination with subqueries to implement paging. An example is as follows:

<code class="language-sql">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 AND @End;</code>

By adjusting the @Start and @End variables, you can specify the desired page range.

The above is the detailed content of How to Implement Query Paging in SQL Server Using OFFSET and FETCH NEXT, and Alternatives for Older Versions?. For more information, please follow other related articles on the PHP Chinese website!