Wednesday, June 23, 2010

MSSQL Paging Query

Assuming:
@page = 1 (INT)
@itemsPerPage = 1000 (INT)


SELECT * FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY myColumn) as RowNumber,*
    FROM myTable
) as tempTable
WHERE
tempTable.RowNumber BETWEEN (((@page - 1) * @itemsPerPage) + 1) AND (@page * @itemsPerPage)


This query will return the first 1000 rows sorted by myColumn

No comments:

Post a Comment