How to Implement Skip and Take Functionality for Paging with SQL Queries?

SQL query paging: implement skip and get functions

In order to implement custom pagination in SQL and retrieve a specific number of records from a large dataset, you need to use skip and get functions. Below is a demonstration of how to achieve this using SQL queries.

Query statement

You have a query that retrieves the PostId from a table containing forum entries, grouped by PostId, and sorted by maximum DateMade per post. This query works fine, but you want to add pagination functionality to it.

Solution

For SQL Server 2012 and above

Starting with SQL Server 2012, you can easily skip and get records using the OFFSET and FETCH NEXT clauses. The modified query looks like this:

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;

For SQL Server 2008

In SQL Server 2008, you can use ROW_NUMBER() to assign a sequence number to each record and then apply a filter to select the required records. Here is the modified query:

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 > @Start AND RowNumber <= @End;

The above is the detailed content of How to Implement Skip and Take Functionality for Paging with SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!