← Tutti gli articoli

TSQL : Paging with ROW_NUMBER

06 April 2011  ·  TSQL · Article  ·  607 visite

SQL Server (from 2005) has a new ROW_NUMBER() function that makes paging task easier.

DECLARE               @PageNum int =1
DECLARE    @PageSize int =10
declare @TotalPages int
WITH ContentsRowNumber AS
(
SELECT ROW_NUMBER() OVER(ORDER BY c.ContentDate desc) AS RowNum,
c.ContentId
FROM dbo.Contents c   inner join ContentsHistory h on c.ContentId=h.ContentId
where h.ContentStatusID = 1  --1=Published
and h.FinishDate is null
)
SELECT r.RowNum, v.*
FROM vwContents v
inner join  ContentsRowNumber r on v.ContentId = r.ContentId
WHERE
v.FinishDate is null
and r.RowNum BETWEEN (@PageNum - 1) * @PageSize + 1
AND (@PageNum * @PageSize)
;
Si è verificato un errore imprevisto. Ricarica

Rejoining the server...

Rejoin failed... trying again in seconds.

Failed to rejoin.
Please retry or reload the page.

The session has been paused by the server.

Failed to resume the session.
Please retry or reload the page.