Let’s suppose that we want to have a page that will display top users, ordered by number of points. A simplified table named Users with only 3 columns looks like that: UserId INT primary key, LoginName Varchar, OverAllPoints INT.
First of all we need a view which calculates the rank of every user according to number of points:
CREATE VIEW UsersTop AS
SELECT
RANK() OVER (ORDER BY OverAllPoints DESC, LoginName) AS RankPoints,
UserId, LoginName, OverAllPoints
FROM Users
Based on the view created above, we can get for example the rank of any user:
SELECT RankPoints
FROM UsersTop
WHERE UserId = @userId
Another useful piece of code, presented below is returning all the users between a start row and end row. Assuming that you want to display page 3 from your top users, and on every page 20 lines are displayed, then stored procedure can be called like that:
EXEC UsersMyTop 41,60
CREATE PROCEDURE UsersMyTop
(@startRow INT, @endRow INT)
AS
BEGIN
SET NOCOUNT ON
SELECT
UserId, LoginName, OverAllPoints, RowNumber
FROM
(SELECT
U.UserId, U.LoginName, U.OverAllPoints,
ROW_NUMBER() OVER(ORDER BY U.OverAllPoints DESC, U.LoginName) AS RowNumber
FROM
Users U ) DerivedTable
WHERE RowNumber >= @startRow AND RowNumber <= @endRow
ORDER BY OverAllPoints DESC
END