CODEWITHSUNDEEP
sqlsql-servert-sql
user4153970
user4153970

Reputation:

SQL Query For pagination has Filter

I'm using T-SQL for pagination like this

SELECT @PageSize = 4,
@Page = 1;
SELECT T.* FROM (((SELECT RANK() OVER(PARTITION BY 'T' ORDER BY ID) AS 
RowIndex, * FROM VideoGallery) AS T
INNER JOIN Video_Category ON T.ID = Video_Category.VideoID)
INNER JOIN VCategories ON VCategories.Name = N'catname' AND 
Video_Category.CategoryID = VCategories.ID)
WHERE T.RowIndex BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * 
@PageSize);

result is like:

ROWINDEX     NAME
   1        backup
   2        email   
   3        fax
   4        GSM
   5        backup
   6        email   
   13       fax
   18       GSM

Problem: in page number 2 only shown 2 result i want to show ROWINDEX 13 and 18 be in page 2

Upvotes: 1

Views: 1556

Answers (3)

Michał Turczyn
Michał Turczyn

Reputation: 37367

This query fails becasue your joins filter out some row numbers, so the aren't consecutive and can have gaps between.

I have rewritten your query with proper indentation and removed unnecessary query nestings. Give it a try:

SELECT @PageSize = 4, @Page = 1;
SELECT * 
FROM (
  SELECT vg.*,
         RANK() OVER(/*PARTITION BY 'T' */ORDER BY vc.VideoID) AS RowIndex
  FROM VideoGallery AS vg
  INNER JOIN Video_Category vc ON vg.ID = vc.VideoID
  INNER JOIN VCategories vc2 ON vc2.[Name] = N'catname' AND vc.CategoryID = vc2.ID
) a
WHERE RowIndex BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * @PageSize);

Also, I have commented part of a code here RANK() OVER(/*PARTITION BY 'T' */ORDER BY ID), uncomment it, if you really want to.

Upvotes: 0

MEdwin
MEdwin

Reputation: 2960

@MorteZa SenFi; have a look at this mock up. I think the issue might actually be from your definition of RowIndex (you should use ROW_NUMBER rather than RANK. Check that the RowIndex Column is actually sequential and consecutive.

enter image description here

See a simple example below:

DECLARE @PageSize INT = 3
DECLARE @Page INT = 2;


DECLARE @tblVideoGallery TABLE (Id INT, Name VARCHAR(20))
INSERT INTO @tblVideoGallery
SELECT 1,'backup' UNION ALL
SELECT 2, 'email' UNION ALL   
SELECT 3, 'fax' UNION ALL 
SELECT 4, 'GSM' UNION ALL 
SELECT 5, 'backup' UNION ALL 
SELECT 6, 'email'  UNION ALL   
SELECT 7, 'fax' UNION ALL 
SELECT 8, 'GSM'






SELECT T.* 
FROM (
SELECT  ROW_NUMBER() OVER(ORDER BY ID) AS RowIndex, *
FROM @tblVideoGallery

) T
--INNER JOIN Video_Category ON T.ID = Video_Category.VideoID)
--INNER JOIN VCategories ON VCategories.Name = N'catname' AND 
--Video_Category.CategoryID = VCategories.ID)
WHERE T.RowIndex BETWEEN ((@Page - 1) * @PageSize + 1) AND (@Page * 
@PageSize);

See result: enter image description here

Upvotes: 0

giorgi02
giorgi02

Reputation: 1063

try this way

ORDER BY ******

OFFSET @ItemsPerPage * (@CurrentPage - 1) ROWS

FETCH NEXT @ItemsPerPage ROWS ONLY

DECLARE @Page int = 1;
DECLARE @PageSize int = 4;

SELECT T.* FROM (((SELECT RANK() OVER(PARTITION BY 'T' ORDER BY ID) AS 
RowIndex, * FROM VideoGallery) AS T
INNER JOIN Video_Category ON T.ID = Video_Category.VideoID)
INNER JOIN VCategories ON VCategories.Name = N'catname' AND 
Video_Category.CategoryID = VCategories.ID)

ORDER BY T.ID
OFFSET @PageSize * (@Page - 1) ROWS
FETCH NEXT @PageSize ROWS ONLY

Upvotes: 1

Related Questions