I have a table called USERS, some of its records are marked as  hiddenRcord, I want to load those records in a custom page in my asp.net webpage with paging enabled, each page contains 10 records. 

I use the statement "SELECT Top 10 tableID,userName FROM USERS WHERE (hiddenRecord=0 AND tableID>@tableID)"

the pagination has 5 links (First,2,3,4,Last), I can of course put the last tableID in link number 2, but I don't know how to do it for the links (3,4,Last).

Is there a way to solve this problem, please help me.

• Moved by Bob BeaucheminMVP 17 hours 27 minutes ago Moved to a relevant forum

Nobody answered me, so I tried and discovered that I can solve the problem by using a view in sql server.

1. You can either use row_number() function. Refer this  or
2. Offset and fetch

 

You can try them based on your SQL version. Hope this helps.

• Proposed as answer by Eric__ZhangMicrosoft contingent staff, Moderator 20 minutes ago

Hi HSawa,

Regarding your description, are you looking for a pagination implementation in SQL Server? If so, you can refer to below sample.

CREATE TABLE [USERS] (
    [tableID] INTEGER NULL,
    [userName] VARCHAR(255) NULL,
    [hiddenRecord] INTEGER NULL
	);
GO

INSERT INTO [USERS]([tableID],[userName],[hiddenRecord]) VALUES(1,'Macy',0),(2,'Gwendolyn',1),(3,'Charissa',0),(4,'Ayanna',1),(5,'Upton',0),(6,'Blaze',0),(7,'Genevieve',0),(8,'Isabella',0),(9,'Bethany',1),(10,'Pearl',1);
INSERT INTO [USERS]([tableID],[userName],[hiddenRecord]) VALUES(11,'Brennan',1),(12,'Rhona',0),(13,'Alexa',1),(14,'Amy',1),(15,'Colorado',1),(16,'Tana',1),(17,'Seth',1),(18,'Carson',0),(19,'Stone',1),(20,'Channing',0);
 

DECLARE @pageNum INT = 3 --PAGE 1
DECLARE @pageSize INT = 3 -- 3 users in a page

;WITH Cte AS
(
SELECT *, (ROW_NUMBER() OVER(ORDER BY tableID)-1)/@pageSize rn FROM USERS
)
SELECT tableID,userName,hiddenRecord FROM CTE
WHERE RN = @pageNum-1

--in SQL SERVER 2012 and onward
SELECT * FROM USERS ORDER BY tableID OFFSET (@pageNum-1)*@pageSize ROWS FETCH NEXT @pageSize ROWS ONLY

DROP TABLE [USERS]

Regarding the last page, have no idea on what value gets passed from your application end, you would have to add IF branch in the T-SQL to get the last page accordingly.

If you have any question, feel free to let me know.