Home > Database > Mysql Tutorial > How to Implement Query Paging in SQL Server Using OFFSET and FETCH NEXT, and Alternatives for Older Versions?

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

DDD
Release: 2025-01-10 20:57:41
Original
520 people have browsed it

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:

SELECT col1, col2, ...
FROM ...
WHERE ...
ORDER BY -- 必须包含 ORDER BY 语句
-- 分页语句
OFFSET 10 ROWS -- 跳过 10 行
FETCH NEXT 10 ROWS ONLY; -- 获取 10 行
Copy after login

Modify original query:

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 行
Copy after login

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:

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;
Copy after login

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!

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template